WGZAAA你好,谢谢你给我留言,<br/>我想完成一下统计:<br/>??乡镇代码???村代码???地类?????面积?<br/>???????20????????1???有林地????0.4<br/>???????10????????2???非林地????0.7<br/>??????10?????????1????有林地????0.1<br/>??????10?????????1????非林地?????0.4<br/>???????30??????????1???有林地?????0.3<br/>?统计结果:<br/>乡镇代码??????????地类?????面积<br/>合计??????????????合计???????1.9?//按地类合计<br/>??????????????????有林地?????0.8<br/>?????????????????????????非林地?????1.1<br/>?10?????????????????合计???????1.2??//分镇统计/按地类<br/>????????????有林地??????0.1<br/>??????????????????非林地??????1.1<br/>?20??????????????????合计???????0.4<br/>???????????????????有林地??????0.4<br/>?30?????????????????合计????????0.3<br/>???????????????????有林地??????0.3<br/>?10????????????????合计???????1.2??//分10镇统计/在分村统计<br/>?????????????有林地??????0.1<br/>??????????????????非林地??????1.1<br/>?1?????????????????合计???????0.5<br/>???????????????????有林地?????0.1<br/>????????????????????非林地????0.4<br/>?2??????????????????合计??????0.7<br/>?????????????????????非林地???0.7<br/>?20?????????????????合计???????0.4?//分20镇统计/在分村统计<br/>????????????有林地??????0.4<br/>?1???????????????????合计???????0.4<br/>????????????????????有林地??????0.4<br/>?30?????????????????合计????????0.3?//分30镇统计/在分村<br/>????????????有林地??????0.3<br/>??1??????????????????合计???????0.4<br/>??????????????????????有林地??????0.4<br/>你给我的sql统计方法非常的简洁,达到如期结果,从中知道你是SQL高手,但我忘了连接地名表了,我想把上面的乡镇代码子段中乡镇代码和村代码改为乡镇名和村名<br/>表结构如下<br/>DECLARE?@tb1?TABLE(<br/>乡镇代码?int,<br/>村代码?int,<br/>乡镇名?nvarchar(10),<br/>村名?nvarchar(10))<br/>INSERT?@tb1<br/>SELECT?10,?1,?'小石',???'小砍'??UNION?ALL<br/>SELECT?10,?2,?'小石',????'围成'?UNION?ALL<br/>SELECT?20,?1,?'南成',???'塘下'???UNION?ALL<br/>SELECT?30,?1,?'惠东',???'惠村'<br/>//////////////////////////////////<br/>DECLARE@tb?TABLE(<br/>乡镇代码?int?<br/>村代码?int<br/>地类?nvarchar(10),<br/>面积?decimal(10,1))<br/>INSERT?@tb<br/>SELECT?20,?1,?N'有林地',?0.4?UNION?ALL<br/>SELECT?10,?2,?N'非林地',?0.7?UNION?ALL<br/>SELECT?10,?1,?N'有林地',?0.1?UNION?ALL<br/>SELECT?10,?1,?N'非林地',?0.4?UNION?ALL<br/>SELECT?30,?1,?N'有林地',?0.3<br/><br/>先前你给我统计sql语句如下<br/>:select?(case?when?地类?is?null?and?isnull(村代码,'0')='0'?then?isnull(乡镇代码,'')?when??地类?is?null?and?isnull(村代码,'0')<>'0'?then?村代码?else?''?end)乡镇代码,isnull(地类,'合计')地类,面积?<br/>from?(select?(case?when?乡镇代码?is?null?and?村代码?is?null?then?0?else?2?end)id,<br/>?????乡镇代码,村代码,地类,sum(面积)面积?from?@tb?group?by?乡镇代码,村代码,地类<br/>?????with?cube?having?not(乡镇代码?is?null?and?村代码?is?not?null)<br/>?????union?select?1,乡镇代码,'0'?村代码,地类,sum(面积)面积?from?@tb?<br/>?????group?by?乡镇代码,地类?with?rollup?having?乡镇代码?is?not?null<br/>)aa?order?by?id,isnull(乡镇代码,0),isnull(村代码,0),isnull(地类,'最')?desc<br/>期待你的答复,谢谢,祝中秋快乐
-
wgzaaa
回复于
2007-09-19
-
我不常看短信，不知道还用得上否，我看到的很多问题，下次还不如告诉地址，答案刚贴到下边，上次写了个感觉不好就没贴<br/>http://community.csdn.net/Expert/topic/5760/5760956.xml?temp=.420788