我有4个领域,例如:
Country, Year, Area,Value
USA , 2010, SQL, 78
USA, 2011, C++, 66
UK, 2012, C, 99
现在在此表中
结果应另外包含:
USA, 2012, C , 0
UK, 2010, SQL, 0
UK, 2012, C++, 0
就像按国家和缺失区域(按表中的条目),年份(按表中的条目)循环一样,需要填写“ 0”。在sql函数/查询/触发器/任何东西中。
我这样尝试过:
SELECT countryID, COUNT ("chart_data"."countryID") AS count
FROM tablename GROUP BY "countryID", "yearID"...
这可能吗?
这是Postgresql或MySQL的答案,但它也可以在其他DBMS上运行:
INSERT INTO tbl (Country, Year, Area, Value)
SELECT Countries.Country, Years.Year, Areas.Area, 0 As Value
FROM
(SELECT DISTINCT Country FROM tbl) Countries CROSS JOIN
(SELECT DISTINCT Year FROM tbl) Years CROSS JOIN
(SELECT DISTINCT Area FROM tbl) Areas
LEFT JOIN tbl ON tbl.Country=Countries.Country
AND tbl.Year=Years.Year
AND tbl.Area=Areas.Area
WHERE
tbl.Value IS NULL -- if it can't be null, otherwise choose any other field
请看这里的小提琴。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句