34,593
社区成员
发帖
与我相关
我的任务
分享
if object_id('[Categroy]') is not null drop table [Categroy]
go
create table [Categroy]([CID] int,[CName] varchar(1))
insert [Categroy]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E' union all
select 6,'F'
--> 测试数据:[BankData]
if object_id('[BankData]') is not null drop table [BankData]
go
create table [BankData]([MID] int,[MName] varchar(1),[CID] int,[CODE] int)
insert [BankData]
select 1,'M',1,1 union all
select 2,'M',2,1 union all
select 3,'H',1,2 union all
select 4,'H',3,2 union all
select 5,'K',1,3 union all
select 6,'K',2,3 union all
select 7,'K',4,3 union all
select 8,'K',5,3 union all
select 9,'F',1,4 union all
select 10,'F',4,4 union all
select 11,'S',1,5 union all
select 12,'S',5,5 union all
select 13,'X',1,6
SELECT Mname,code
FROM (
SELECT Mname,code,ROW_NUMBER()OVER(PARTITION BY code ORDER BY mid)id
FROM [BankData] b
INNER JOIN (
SELECT [cid]
FROM [Categroy]
WHERE [cname] IN ( 'A', 'B' ) ) c ON b.cid=c.cid
)a
GROUP BY Mname,code
HAVING MAX(id)=(SELECT COUNT(1) FROM [Categroy]
WHERE [cname] IN ( 'A', 'B' ) )
/*
Mname code
----- -----------
M 1
K 3
*/
if object_id('[Categroy]') is not null drop table [Categroy]
go
create table [Categroy]([CID] int,[CName] varchar(1))
insert [Categroy]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E' union all
select 6,'F'
--> 测试数据:[BankData]
if object_id('[BankData]') is not null drop table [BankData]
go
create table [BankData]([MID] int,[MName] varchar(1),[CID] int,[CODE] int)
insert [BankData]
select 1,'M',1,1 union all
select 2,'M',2,1 union all
select 3,'H',1,2 union all
select 4,'H',3,2 union all
select 5,'K',1,3 union all
select 6,'K',2,3 union all
select 7,'K',4,3 union all
select 8,'K',5,3 union all
select 9,'F',1,4 union all
select 10,'F',4,4 union all
select 11,'S',1,5 union all
select 12,'S',5,5 union all
select 13,'X',1,6
SELECT DISTINCT [code],Mname
FROM [BankData]
WHERE [code] NOT IN (
SELECT code
FROM [BankData] b
WHERE [cid] NOT IN ( SELECT DISTINCT
[cid]
FROM [Categroy]
WHERE [cname] IN ( 'A','B') ) )
ORDER BY code
看下 这个数据 就是有问题的, 满足 组合条件的数据 只有 M 和 K。----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-28 15:57:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[Categroy]
if object_id('[Categroy]') is not null drop table [Categroy]
go
create table [Categroy]([CID] int,[CName] varchar(1))
insert [Categroy]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D' union all
select 5,'E' union all
select 6,'F'
--> 测试数据:[BankData]
if object_id('[BankData]') is not null drop table [BankData]
go
create table [BankData]([MID] int,[MName] varchar(1),[CID] int,[CODE] int)
insert [BankData]
select 1,'M',1,1 union all
select 2,'M',2,1 union all
select 3,'H',1,2 union all
select 4,'H',3,2 union all
select 5,'K',1,3 union all
select 6,'K',2,4 union all
select 7,'K',4,5 union all
select 8,'K',5,6 union all
select 9,'F',6,4 union all
select 10,'F',5,6
--------------开始查询--------------------------
SELECT DISTINCT [code],Mname
FROM [BankData]
WHERE [code] NOT IN (
SELECT code
FROM [BankData] b
WHERE [cid] NOT IN ( SELECT DISTINCT
[cid]
FROM [Categroy]
WHERE [cname] IN ( 'A', 'B','D' ) ) )
ORDER BY code
----------------结果----------------------------
/*
code Mname
----------- -----
1 M
3 K
5 K
*/
select code,mname from(
select code,mname,
cid=(select ','+cast(cid as varchar) from bankdata where code=b.code for xml path(',')) from bankdata b
group by code,mname) as c
where cid=',1,2'
没测试不知道行不行。----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-28 15:57:06
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------------------------------------------
--> 测试数据:[Categroy]
if object_id('[Categroy]') is not null drop table [Categroy]
go
create table [Categroy]([CID] int,[CName] varchar(1))
insert [Categroy]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
--> 测试数据:[BankData]
if object_id('[BankData]') is not null drop table [BankData]
go
create table [BankData]([MID] int,[MName] varchar(1),[CID] int,[CODE] int)
insert [BankData]
select 1,'M',1,1 union all
select 2,'M',2,1 union all
select 3,'H',1,2 union all
select 4,'H',3,2 union all
select 5,'K',1,3 union all
select 6,'K',2,3
--------------开始查询--------------------------
SELECT DISTINCT [code],Mname
FROM [BankData]
WHERE [code] NOT IN (
SELECT code
FROM [BankData] b
WHERE [cid] NOT IN ( SELECT DISTINCT
[cid]
FROM [Categroy]
WHERE [cname] IN ( 'A', 'B' ) ) )
ORDER BY code
----------------结果----------------------------
/*
code Mname
----------- -----
1 M
3 K
*/
select code,mname from bankdata b where exists(select 1 from bankdata where code=b.code and
cid>b.cid) and b.code in(1,3)