我有一个SQL查询的问题,我将尝试解释我想做什么。这是我的查询,返回一些结果行。
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
sum(scd.scdkiek) as count_of_goods,
min(scn.scndat) as `date`
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN scn ON scn.scnid = scd.scnid
INNER JOIN sandeliai ON sandeliai.paid = sck.paid
WHERE sck.prid = 1376 GROUP BY sckid
这个查询返回结果的"表1":
表1:sckid | prid | sckcen | count_of_goods | date |
123 | 1376 | 10009 | 0 | 2012-12-31 |
124 | 1376 | 10007 | 15 | 2013-01-25 |
125 | 1376 | 10005 | 0 | 2013-02-13 |
126 | 1376 | 10000 | 18 | 2013-03-15 |
但是我只需要一行所有数据按prid
分组,我写了这个查询:
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
sum(scd.scdkiek) as count_of_goods,
min(scn.scndat) as `date`
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN scn ON scn.scnid = scd.scnid
INNER JOIN sandeliai ON sandeliai.paid = sck.paid
WHERE sck.prid = 1376 GROUP BY prid
然后我在表中得到一行数据:
表2:sckid | prid | sckcen | count_of_goods | date |
123 | 1376 | 10009 | 23 | 2012-12-31 |
这似乎都是正确的,但在date
字段中,我需要从表1返回最早的日期,其中count_of_goods> 0,所以我需要这个结果:
sckid | prid | sckcen | count_of_goods | date |
123 | 1376 | 10009 | 23 | 2013-01-25 |
那么我怎样才能得到表3中的结果呢?
我认为我最初得到的答案可能过于复杂了。假设scd。scdkiek是数字而不是id,那么您可以通过简单地更改INNER JOIN条件来得到您想要的,即
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
scn.scndat,
SUM(scd.scdkiek) AS count_of_goods
FROM sck
INNER JOIN scd
ON scd.sckid = sck.sckid
AND scd.scdkiek > 0
INNER JOIN sandeliai
ON sandeliai.paid = sck.paid
INNER JOIN scn
ON scn.scnid = scd.scnid
WHERE sck.prid = 1376
GROUP BY
sck.prid
如果做不到这一点,我会考虑使用子查询。子查询将计算每行的count_of_goods。然后,外部查询将使用它来决定是否将该行的日期设置为NULL。MIN(…somedate…,NULL)将返回日期。这反过来意味着您应该获得该行上的count_of_goods不为NULL的最小日期。
子查询按日期分组,因为您正在查找count_of_goods> 0的日期。
这只是一个选择,当然,可能还有其他更有效的方法,但我相信这将达到您的目的
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
SUM(d.count_of_goods) AS count_of_goods,
MIN(IF(d.count_of_goods>0,d.scndat,NULL)) AS `date`
FROM (
-- Get the data and group it by date
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
scn.scndat,
SUM(scd.scdkiek) AS count_of_goods
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN sandeliai ON sandeliai.paid = sck.paid
INNER JOIN scn ON scn.scnid = scd.scnid
WHERE sck.prid = 1376
GROUP BY
sck.prid, -- may not really be needed
scn.scndat
) AS d
GROUP BY d.prid
尝试使用having
,例如
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
sum(scd.scdkiek) as count_of_goods,
min(scn.scndat) as `date`
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN scn ON scn.scnid = scd.scnid
INNER JOIN sandeliai ON sandeliai.paid = sck.paid
WHERE sck.prid = 1376 GROUP BY prid HAVING count_of_goods > 0;
这应该对你有用。这可能会跳过一些行计数= 0,我认为应该是好的(只是我个人的意见)。
请把这作为一个起点,而不是最终的解决方案。
Edit: Updated sum(…)in having with alias
现在我的问题不是很合乎逻辑,我试图在我的问题中结合两个查询,然后我得到一个很好的答案,这是我的解决方案:
SELECT
sck.sckid,
sck.prid,
sck.paid,
sck.sckcen,
sum(scd.scdkiek) as count_of_goods,
(SELECT
scn.scndat
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN scn ON scn.scnid = scd.scnid
WHERE sck.prid = 1376 GROUP BY sck.sckid having sum(scd.scdkiek) > 0 ORDER BY scn.scndat LIMIT 1) as `date`
FROM sck
INNER JOIN scd ON scd.sckid = sck.sckid
INNER JOIN scn ON scn.scnid = scd.scnid
WHERE sck.prid = 1376 GROUP BY prid;