---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-03-25 09:16:01 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([Type] int,[A] int,[B] int) insert [tb] select 1,70210001,70210003 union all select 0,70120002,72230002 union all select 0,71340001,72220003 union all select 1,73320003,72230002 union all select 0,73330002,70210001 union all select 1,70210001,73220001 union all select 0,73330003,70210001 --------------开始查询-------------------------- SELECT * FROM ( SELECT * FROM tb WHERE type=1 AND a LIKE '%0001%' UNION ALL SELECT * FROM tb WHERE type=0 AND b LIKE '%0001%' ) AS t WHERE a<>b ----------------结果---------------------------- /* Type A B ----------- ----------- ----------- 1 70210001 70210003 1 70210001 73220001 0 73330002 70210001 0 73330003 70210001(4 行受影响)*/
这样查出来的结果是 1 70210001 70210003 0 73330002 70210001 有两条结果 但是我只想要一条70210001的结果 select type , A ,max(B) from 表 where ( A like '% 0001%' and Type = 1 ) or ( B like '% 0001%' and Type = 0 ) group by B
select max(type), A ,max(B) from 表 where ( A like '% 0001%' and Type = 1 ) or ( B like '% 0001%' and Type = 0 ) group by A
SELECT TOP 1 * FROM ( SELECT * FROM tb WHERE type=1 AND a LIKE '%0001%' UNION ALL SELECT * FROM tb WHERE type=0 AND b LIKE '%0001%' ) AS t WHERE a<>b AND (a='70210001' OR b='70210001')不知道是不是这个意思。
这样查出来的结果是 1 70210001 70210003 0 73330002 70210001 有两条结果 但是我只想要一条70210001的结果 请大家原谅我表达能力太差了 Type A B 1 70210001 0 0 0 70210001 0 0 70220001 1 70230001 0 1 70230001 0 0 0 70220001 假设这6条数据吧 我想要的结果是: 1 70210001 0 0 0 70220001 1 70230001 0这样吗: if object_id('[tb]') is not null drop table [tb] go create table [tb]([Type] int,[A] int,[B] int) insert [tb] select 1,70210001,0 union all select 0,0,70210001 union all select 0,0,70220001 union all select 1,70230001,0 union all select 1,70230001,0 union all select 0,0,70210001 ;with t as ( select type,a,b from tb where ( A like '%0001%' and Type = 1 ) or ( B like '%0001%' and Type = 0 ) group by type,a,b )select * from t where not exists(select 1 from t tt where t.B = tt.A and t.A = 0) /* type a b 1 70210001 0 1 70230001 0 0 0 70220001 */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-25 09:16:01
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Type] int,[A] int,[B] int)
insert [tb]
select 1,70210001,70210003 union all
select 0,70120002,72230002 union all
select 0,71340001,72220003 union all
select 1,73320003,72230002 union all
select 0,73330002,70210001 union all
select 1,70210001,73220001 union all
select 0,73330003,70210001
--------------开始查询--------------------------
SELECT * FROM
(
SELECT * FROM tb WHERE type=1 AND a LIKE '%0001%'
UNION ALL
SELECT * FROM tb WHERE type=0 AND b LIKE '%0001%'
) AS t
WHERE
a<>b
----------------结果----------------------------
/* Type A B
----------- ----------- -----------
1 70210001 70210003
1 70210001 73220001
0 73330002 70210001
0 73330003 70210001(4 行受影响)*/
1 70210001 70210003
0 73330002 70210001
有两条结果 但是我只想要一条70210001的结果
select type , A ,max(B) from 表
where ( A like '% 0001%' and Type = 1 ) or
( B like '% 0001%' and Type = 0 ) group by B
select max(type), A ,max(B) from 表
where ( A like '% 0001%' and Type = 1 ) or
( B like '% 0001%' and Type = 0 ) group by A
(
SELECT * FROM tb WHERE type=1 AND a LIKE '%0001%'
UNION ALL
SELECT * FROM tb WHERE type=0 AND b LIKE '%0001%'
) AS t
WHERE
a<>b AND (a='70210001' OR b='70210001')不知道是不是这个意思。
1 70210001 70210003
0 73330002 70210001
有两条结果 但是我只想要一条70210001的结果
请大家原谅我表达能力太差了
Type A B
1 70210001 0
0 0 70210001
0 0 70220001
1 70230001 0
1 70230001 0
0 0 70220001
假设这6条数据吧 我想要的结果是:
1 70210001 0
0 0 70220001
1 70230001 0
1 70210001 70210003
0 73330002 70210001
有两条结果 但是我只想要一条70210001的结果
请大家原谅我表达能力太差了
Type A B
1 70210001 0
0 0 70210001
0 0 70220001
1 70230001 0
1 70230001 0
0 0 70220001
假设这6条数据吧 我想要的结果是:
1 70210001 0
0 0 70220001
1 70230001 0这样吗:
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Type] int,[A] int,[B] int)
insert [tb]
select 1,70210001,0 union all
select 0,0,70210001 union all
select 0,0,70220001 union all
select 1,70230001,0 union all
select 1,70230001,0 union all
select 0,0,70210001
;with t
as
(
select type,a,b
from tb
where ( A like '%0001%' and Type = 1 ) or
( B like '%0001%' and Type = 0 )
group by type,a,b
)select *
from t
where not exists(select 1 from t tt where t.B = tt.A and t.A = 0)
/*
type a b
1 70210001 0
1 70230001 0
0 0 70220001
*/