假设有表"tbJamKind (JamKind primary key, Descript)"存放JamKind的所有数据 select a.JamKind, isnull(b.CT,0) from tbJamKind a left join ( SELECT JamKind, COUNT(*) AS CT FROM tblTrafficJam GROUP BY JamKind ) as b on a.JamKind=b.JamKind
这个不大对吧. 如果你要加上CT为0的行, 那么他jamkind这个字段的值该怎么放啊? 和你group by jamkind的本意矛盾了或者你单独加上一行SELECT JamKind, COUNT(*) AS CT FROM tblTrafficJam GROUP BY JamKind union select 'Others' as JamKind ,'0' as ct FROM tblTrafficJam
SELECT jamkind.Description AS jamkindDesc, COUNT(*) AS CT FROM tblTrafficJam AS Jam LEFT OUTER JOIN tblDescJamKind AS jamkind ON Jam.JamKind = jamkind.Encode GROUP BY jamkind.Description我是这样写的,结果应该有4行的,但count为0的那行并没有显示出来。
select a.JamKind, isnull(b.CT,0)
from tbJamKind a
left join (
SELECT JamKind, COUNT(*) AS CT
FROM tblTrafficJam
GROUP BY JamKind
) as b on a.JamKind=b.JamKind
如果你要加上CT为0的行,
那么他jamkind这个字段的值该怎么放啊?
和你group by jamkind的本意矛盾了或者你单独加上一行SELECT JamKind, COUNT(*) AS CT
FROM tblTrafficJam
GROUP BY JamKind
union
select 'Others' as JamKind ,'0' as ct
FROM tblTrafficJam
encode discription
1 行驶不畅
2 交通拥挤
3 交通拥堵
4 交通堵塞
两表连接???说清楚很累吗?
请楼主把表中的数据贴出来看看。
这是参数表
tbldescjamkind
encode discription
1 行驶不畅
2 交通拥挤
3 交通拥堵
4 交通堵塞
id parentid neteork milepost jamkind
07736e95c8458e542689dc25ba6830d6daa 07736e95c8458e542689dc25ba6830d6daa S201 1234.101 3
078a338da4a5c54444091c3aa31dc367b04 078a338da4a5c54444091c3aa31dc367b04 g010 321.000 1
07L7177ef43c9f642e882bbbf55892d8704 07L7177ef43c9f642e882bbbf55892d8704 S108 123.001 1
07S3931b212826d40489a4be3c72472284c 07S3931b212826d40489a4be3c72472284c G045 4501.000 3
07S67858c67cd3147e695b2ef3a499368c5 07S0d758870b4c14df1b0628fa5f1cf7ffd G045 123.000 1
07S6adf229ce118497d9b2571650a7799bd 07Sa7608b0e9eba46e386c496072fc7c681 G312 123.000 1
FROM tblTrafficJam AS Jam LEFT OUTER JOIN
tblDescJamKind AS jamkind ON Jam.JamKind = jamkind.Encode
GROUP BY jamkind.Description我是这样写的,结果应该有4行的,但count为0的那行并没有显示出来。