分类表(Categroy)数据:
CID CName
-------------
1 A
2 B
3 C主表(BankData)数据:MID MName CID CODE
------------------------
1 M 1 1
2 M 2 1
3 H 1 2
4 H 3 2
5 K 1 3
6 K 2 3现在 我想根据 分类 A + B 查询 主表数据 结果集为:CODE MName
------------------
1 M
3 K-------------------------------------------------------
哪位大牛 可以帮小弟 写下? 在线等 完事立马结贴把分送上!!!数据库SQL行业数据
CID CName
-------------
1 A
2 B
3 C主表(BankData)数据:MID MName CID CODE
------------------------
1 M 1 1
2 M 2 1
3 H 1 2
4 H 3 2
5 K 1 3
6 K 2 3现在 我想根据 分类 A + B 查询 主表数据 结果集为:CODE MName
------------------
1 M
3 K-------------------------------------------------------
哪位大牛 可以帮小弟 写下? 在线等 完事立马结贴把分送上!!!数据库SQL行业数据
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)
组合分类查询的, 数据 M 和 K 都包含了 分类 A 和 B . 数据 H 有的分类为 A 和 C ,所以 就没有 3,4条的数据了 。希望给解决下 谢了先!
-- 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(
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'没测试不知道行不行。
不好意思
exists前面少写了个not.
不好意思
exists前面少写了个not.查不到数据啊,多谢百忙中来回帖 帮忙再看下。
-- 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
*/
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。
不是,主表数据是这样的 一个MName 对应一个CODE 但可以对应 多个 CID . 现在想 通过 【组合分类】 CID (比如 分类 A 和 B) 来查询 主表信息。以现有的 SQL 数据 查的理想结果为:MName CODE
---------------
M 1
K 3
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
*/