22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE test
(
识别 INT ,
关键词 VARCHAR(10) ,
内容 VARCHAR(10)
)
INSERT INTO test
SELECT 6 ,
'张三' ,
'中国'
UNION ALL
SELECT NULL ,
'张三' ,
NULL
UNION ALL
SELECT NULL ,
'张三' ,
'中国北京2'
UNION ALL
SELECT NULL ,
'李四' ,
'33'
UNION ALL
SELECT NULL ,
'王五' ,
NULL
UNION ALL
SELECT NULL ,
'王五' ,
'中国北京2'
SELECT * FROM test a
where exists (
select 1
from test b
where b.关键词 = a.关键词
and b.识别 = 6
)
and not exists (
select 1
from test b
where b.关键词 = a.关键词
and b.识别 = 6
and isnull(a.识别,0) <> 6
)
or not exists (
select 1
from test b
where b.关键词 = a.关键词
and b.识别 = 6
)
and not exists (
select 1
from test b
where b.关键词 = a.关键词
and (isnull(b.识别,0)< isnull(a.识别,0)
or isnull(b.识别,0)= isnull(a.识别,0)
and isnull(b.内容,'')> isnull(a.内容,'')
)
)
--结果
识别 关键词 内容
6 张三 中国
NULL 李四 33
NULL 王五 中国北京2
CREATE TABLE test
(
识别 INT ,
关键词 VARCHAR(10) ,
内容 VARCHAR(10)
)
INSERT INTO test
SELECT 6 ,
'张三' ,
'中国'
UNION ALL
SELECT 3 ,
'张三' ,
NULL
UNION ALL
SELECT NULL ,
'张三' ,
'中国北京2'
UNION ALL
SELECT NULL ,
'李四' ,
'33'
with yy as(
select 识别,关键词,内容,ROW_NUMBER() over(partition by 关键词 order by 识别 desc)ROW
from test )
select 识别,关键词,内容 from yy where ROW=1
SELECT *
FROM test a
WHERE 关键词 IN ( SELECT 关键词
FROM test
GROUP BY 关键词
HAVING COUNT(1) > 1 )
AND 识别 IS NOT NULL
UNION ALL
SELECT *
FROM test a
WHERE 关键词 IN ( SELECT 关键词
FROM test
GROUP BY 关键词
HAVING COUNT(1) = 1 )
CREATE TABLE test
(
识别 INT ,
关键词 VARCHAR(10) ,
内容 VARCHAR(10)
)
INSERT INTO test
SELECT 6 ,
'张三' ,
'中国'
UNION ALL
SELECT NULL ,
'张三' ,
NULL
UNION ALL
SELECT NULL ,
'张三' ,
'中国北京2'
UNION ALL
SELECT NULL ,
'李四' ,
'33'
SELECT * FROM test a
WHERE 关键词 IN (
SELECT 关键词
FROM test
GROUP BY 关键词
HAVING COUNT(1)>1) AND 识别 IS NOT NULL
UNION ALL
SELECT * FROM test a
WHERE 关键词 IN (
SELECT 关键词
FROM test
GROUP BY 关键词
HAVING COUNT(1)=1)
/*
识别 关键词 内容
----------- ---------- ----------
6 张三 中国
NULL 李四 33
(2 行受影响)
*/