表数据:
a b
1 10
2 20
3 30
4 40
5 50请帮忙写个SQL,得到表里的数据 b列大于20和大于40的各有多少条数据,即
result:
>20 3
>40 1先谢谢了。 ---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50--SQL查询如下:SELECT
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END
HAVING CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END IS NOT NULL
这个SQL得到的结果是大于20的 大于40的
----------- -----------
20 50
30
40
---3个 --1个
而真正要的结果是
大于20的 大于40的
----------- -----------
30 50
40
50
---3个 --1个 区别在于大于40的数据‘50’是同时属于大于20的
a b
1 10
2 20
3 30
4 40
5 50请帮忙写个SQL,得到表里的数据 b列大于20和大于40的各有多少条数据,即
result:
>20 3
>40 1先谢谢了。 ---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50--SQL查询如下:SELECT
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END
HAVING CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END IS NOT NULL
这个SQL得到的结果是大于20的 大于40的
----------- -----------
20 50
30
40
---3个 --1个
而真正要的结果是
大于20的 大于40的
----------- -----------
30 50
40
50
---3个 --1个 区别在于大于40的数据‘50’是同时属于大于20的
解决方案 »
- 请问sql server 有日志纪录某个table 里面的哪些资料被哪台计算机,哪个user ,什幺时间删除的幺?
- 请教一个触发器问题
- 请教一个SQL难题,路过的看看!
- (求助)sql server 2008安装提示说不能读取受保护内存,其他内存损坏
- 百万级]通用存储过程.分页存储过程. 调用问题。。。。。。。。。。。。。。。。。。。。
- verchar转int型的时候出现小问题,请达人帮忙看看
- 求高人帮修改下SQL查询语句……
- sqlserver2005 通过外网连接速度很慢,2000就很快,怎么解决这个问题?
- 高分征求一个数据库结构设计方案,很急!详细请进入!
- 跪求!!!
- 将varchar转换为int时出错??不解,求助!~
- Truncate table之后不能插入数据了?
没错?
SELECT
CASE WHEN B>40 THEN '>40'
WHEN B>=20 THEN '>20' END , >40的数据,也肯定要在>20里面,现在两者都分开了
INSERT @TB
SELECT 1, 10 UNION ALL
SELECT 2, 20 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 4, 40 UNION ALL
SELECT 5, 50SELECT SUM(CASE WHEN B>20 THEN 1 ELSE 0 END) AS [>20],
SUM(CASE WHEN B>40 THEN 1 ELSE 0 END) AS [>50]
FROM @TB
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50;with c
as
(
SELECT
[>40]=sum(CASE WHEN B>40 THEN 1 else 0 end),
[>20]=sum(case WHEN B>=20 THEN 1 else 0 END)
FROM @T
)
select
Col,con
from
c
unpivot
(con for Col in([>40],[>20]) )t
(5 個資料列受到影響)
Col con
-------------------------------------------------------------------------------------------------------------------------------- -----------
>40 1
>20 4(2 個資料列受到影響)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50;with c
as
(
SELECT
[>40]=sum(CASE WHEN B>40 THEN 1 else 0 end),
[>20]=sum(case WHEN B>20 THEN 1 else 0 END)
FROM @T
)
select
Col,con
from
c
unpivot
(con for Col in([>40],[>20]) )t
(5 個資料列受到影響)
Col con
-------------------------------------------------------------------------------------------------------------------------------- -----------
>40 1
>20 3(2 個資料列受到影響)
DECLARE @T TABLE (a INT,b INT)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50 SQL查询如下: (SELECT
CASE WHEN B>20 THEN '>20'
END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>20 THEN '>20'
END
HAVING CASE WHEN B>20 THEN '>20'
END IS NOT NULL )
union all
(SELECT
CASE WHEN B>40 THEN '>40'
END ,
COUNT(*)
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
END
HAVING CASE WHEN B>40 THEN '>40'
END IS NOT NULL )
修改的小梁的,测试通过,呵呵
(CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END) as a ,
COUNT(*) as b
FROM @T
GROUP BY
CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END
HAVING CASE WHEN B>40 THEN '>40'
WHEN B>20 THEN '>20' END IS NOT NULL )
试下这个看
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50
select '20' a,(select COUNT(1) from @T where b>20) b
union all
select '40' a,(select COUNT(1) from @T where b>40) b何必那么麻烦
听说是2005才有的,要兼容下2000的啊,
另外不希望要 ‘union all'的,性能不太好吧
from tb
union all
select distinct '>40',cnt=(select count(*) from tb where b>40)
from tb a cnt
---- -----------
>20 3
>40 1(2 行受影响)
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50 select
'>'+t.col as col,count(t2.a) as con
from
(select '20' as col union all select '40' as col)t
cross join
@T t2
where t.col<t2.b
group by t.col
INSERT INTO @T
SELECT 1,10 UNION ALL
SELECT 2,20 UNION ALL
SELECT 3,30 UNION ALL
SELECT 4,40 UNION ALL
SELECT 5,50
select '>20',cnt=count(*)from @t where b>20
union all
select '>40',cnt=count(*) from @t where b>40/*--------------
>20 3
>40 1*/