34,576
社区成员
发帖
与我相关
我的任务
分享
WITH T1(II,NAME,VALUE) AS(SELECT 123,'颜色','白色' union all
SELECT 123,'容量','32' union all
SELECT 124,'颜色','白色' union all
select 124,'容量','64')
--SELECT * FROM T1MAX
--PIVOT (MAX(VALUE)FOR II IN([123],[124]))MM
select * from t1
pivot (MAX(VALUE) FOR NAME IN([容量],[颜色]))MM
/
II 容量 颜色
123 32 白色
124 64 白色
//* 测试数据
WITH table1(id,invid,name,value) AS (
SELECT 16,85,'颜色','白色' UNION ALL
SELECT 01,85,'容量','32' UNION ALL
SELECT 94,98,'颜色','白色' UNION ALL
SELECT 75,98,'容量','64'
)
*/
SELECT invid
FROM table1
WHERE (name='颜色' AND value='白色')
OR (name='容量' AND value='32')
GROUP BY invid
HAVING COUNT(DISTINCT name) = 2
invid
-----------
85
select Invid
from tablename as a
where name = '颜色' and value = '白色'
and exists (
select 1
from tablename as b
where b.Invid = a.Invid and b.name = '容量' and b.value = '32'
)
select invid
from [表名]
where (name='容量' and value='32')
or (name='颜色' and value='白色')
group by invid
having count(distinct name)=2
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T(Invid int,name nvarchar(31),value nvarchar(31))
Insert #T
select 1,'颜色','白色' union all
select 1,'容量','32' union all
select 2,'颜色','白色' union ALL
select 2,'颜色','64'
Go
--测试数据结束
SELECT Invid
FROM #T
WHERE ( name = '容量'
AND value = '32'
)
OR ( name = '颜色'
AND value = '白色'
)
GROUP BY Invid
HAVING COUNT(Invid) = 2