22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #t(番号 NVARCHAR(20),部门 NVARCHAR(20),区分 INT)
INSERT INTO #t
SELECT '1.','aa.',0
UNION ALL
SELECT '2.','aa.', 1
UNION ALL
SELECT '3.','bb.', 0
UNION ALL
SELECT '4.','cc.', 1
UNION ALL
SELECT '5.','dd.', 0
UNION ALL
SELECT '6.','ee.', 1
UNION ALL
SELECT '7.','aa.', 0
UNION ALL
SELECT '8.','ee.', 1
UNION ALL
SELECT '9.','aa.', 1
UNION ALL
SELECT '10.','ff.', 0
--1问
select sum(case when 区分=0 then 1 else 0 end) as 区分0
,sum(case when 区分=1 then 1 else 0 end) as 区分1
from (
select 部门,区分 from #t
union
select 部门,区分 from #t
)a
/*
区分0 区分1
4 3
*/
--2问
;with cte as
(select case when sum(case when 区分=0 then 1 else 0 end) >
sum(case when 区分=1 then 1 else 0 end) then 1 else 0 end as 区分少的
from (
select 部门,区分 from #t
union
select 部门,区分 from #t
)a
)select * from #t where 番号 =(
select MIN(番号) from #t where 区分=(select * from cte)
)
/*
番号 部门 区分
-------------------- -------------------- -----------
2. aa. 1
*/
CREATE TABLE TABLENAME1(番号 NVARCHAR(20),部门 NVARCHAR(20),分区 INT)
INSERT INTO TABLENAME1
SELECT '01.','aa.',0
UNION ALL
SELECT '02.','aa.', 1
UNION ALL
SELECT '03.','bb.', 0
UNION ALL
SELECT '04.','cc.', 1
UNION ALL
SELECT '05.','dd.', 0
UNION ALL
SELECT '06.','ee.', 1
UNION ALL
SELECT '07.','aa.', 0
UNION ALL
SELECT '08.','ee.', 1
UNION ALL
SELECT '09.','aa.', 1
UNION ALL
SELECT '10.','ff.', 0
SELECT 分区, Count(DISTINCT 部门),(SELECT 部门 FROM TABLENAME1 WHERE 番号=MAX(T0.番号))
FROM TABLENAME1 T0
GROUP BY 分区
DROP TABLE TABLENAME1
/*
(10 行受影响)
分区
----------- -------------------- ----------- --------------------
0 10. 4 ff.
1 09. 3 aa.
(2 行受影响)
*/
CREATE TABLE TABLENAME1(番号 NVARCHAR(20),部门 NVARCHAR(20),分区 INT)
INSERT INTO TABLENAME1
SELECT '1.','aa.',0
UNION ALL
SELECT '2.','aa.', 1
UNION ALL
SELECT '3.','bb.', 0
UNION ALL
SELECT '4.','cc.', 1
UNION ALL
SELECT '5.','dd.', 0
UNION ALL
SELECT '6.','ee.', 1
UNION ALL
SELECT '7.','aa.', 0
UNION ALL
SELECT '8.','ee.', 1
UNION ALL
SELECT '9.','aa.', 1
UNION ALL
SELECT '10.','ff.', 0
SELECT *
FROM TABLENAME1 T0
WHERE NOT EXISTS(SELECT 1 FROM TABLENAME1 WHERE 部门=T0.部门 AND 分区=T0.分区 AND 番号<T0.番号 )
DROP TABLE TABLENAME1
/*
(10 行受影响)
番号 部门 分区
-------------------- -------------------- -----------
1. aa. 0
2. aa. 1
3. bb. 0
4. cc. 1
5. dd. 0
6. ee. 1
10. ff. 0
(7 行受影响)
*/