SQL学徒请教GROUP BY语句简化

lk1822

1. TRANSFORM Count(商务通.对话类型) AS 对话类型之计数
   
2. SELECT Switch([初次访问网址] Like "*www.srzc.com/*",29,[初次访问网址] Like "*www.zjrb.cn/*",30,[初次访问网址] Like "*www.zsnews.cn/*",31,[初次访问网址] Like "*www.meizhou.cn/*",32,[初次访问网址] Like "*www.tongren.gov.cn/*",33,[初次访问网址] Like "*news.xmnn.cn/*",34,[初次访问网址] Like "*www.beiww.com/*",35,[初次访问网址] Like "*www.lfnews.cn/*",36,[初次访问网址] Like "*www.hljnews.cn/*",37,[初次访问网址] Like "*www.wnnews.cn/*",38,[初次访问网址] Like "*www.sz.js.cn/*",39,[初次访问网址] Like "*www.hynews.net/*",40,[初次访问网址] Like "*www.sxgov.cn/*",41,[初次访问网址] Like "*www.0746news.com/*",42) AS ID, Count(商务通.对话类型) AS 对话类型之计数1
   
3. FROM 商务通
   
4. GROUP BY Switch([初次访问网址] Like "*www.srzc.com/*",29,[初次访问网址] Like "*www.zjrb.cn/*",30,[初次访问网址] Like "*www.zsnews.cn/*",31,[初次访问网址] Like "*www.meizhou.cn/*",32,[初次访问网址] Like "*www.tongren.gov.cn/*",33,[初次访问网址] Like "*news.xmnn.cn/*",34,[初次访问网址] Like "*www.beiww.com/*",35,[初次访问网址] Like "*www.lfnews.cn/*",36,[初次访问网址] Like "*www.hljnews.cn/*",37,[初次访问网址] Like "*www.wnnews.cn/*",38,[初次访问网址] Like "*www.sz.js.cn/*",39,[初次访问网址] Like "*www.hynews.net/*",40,[初次访问网址] Like "*www.sxgov.cn/*",41,[初次访问网址] Like "*www.0746news.com/*",42)
   
5. PIVOT 商务通.对话类型;

复制代码

以上是我自己写的SQL，的确可以达到我想要的统计目的，但是由于字符限制，没法加入更多的查询条件。
不知道有没有办法简化上面的代码，能让我写更多的内容，如能赐教，十分感谢。PS：我使用的是ACCESS2010

---

wudixin96

{:soso_e103:}你不会再嵌套一层么，非得在GROUP BY后面写这么长么？？

tomyi

不知道 union如何

lk1822

wudixin96 发表于 2012-12-15 16:12
你不会再嵌套一层么，非得在GROUP BY后面写这么长么？？

说实话大哥，我真不会，能详细点说下嘛？

lk1822

自己顶一个，别沉啊@

wuxiang_123

1. TRANSFORM Count(商务通.对话类型) AS 对话类型之计数
   
2. select ID,COUNT(商务通.对话类型) AS 对话类型之计数1 FROM
   
3. (SELECT Switch([初次访问网址] Like "*www.srzc.com/*",29,[初次访问网址] Like "*www.zjrb.cn/*",30,[初次访问网址] Like "*www.zsnews.cn/*",31,[初次访问网址] Like "*www.meizhou.cn/*",32,[初次访问网址] Like "*www.tongren.gov.cn/*",33,[初次访问网址] Like "*news.xmnn.cn/*",34,[初次访问网址] Like "*www.beiww.com/*",35,[初次访问网址] Like "*www.lfnews.cn/*",36,[初次访问网址] Like "*www.hljnews.cn/*",37,[初次访问网址] Like "*www.wnnews.cn/*",38,[初次访问网址] Like "*www.sz.js.cn/*",39,[初次访问网址] Like "*www.hynews.net/*",40,[初次访问网址] Like "*www.sxgov.cn/*",41,[初次访问网址] Like "*www.0746news.com/*",42) AS ID, 商务通.对话类型
   
4. FROM 商务通)
   
5. GROUP BY ID
   
6. PIVOT 商务通.对话类型;

复制代码

lk1822

wuxiang_123 发表于 2012-12-16 10:33

非常感谢版主大大！！