27,580
社区成员
发帖
与我相关
我的任务
分享
use students3;
go
if object_id('product')is not null
drop table product;
go
create table product
(field char(4),value varchar(2),product_id int)
insert into product
select '颜色','红',1 union all
select '颜色','黄',1 union all
select '颜色','红',2 union all
select '尺码','35',1 union all
select '尺码','36',1 union all
select '尺码','37',1 union all
select '尺码','35',2 union all
select '尺码','36',2;
go
declare @color as varchar(2)
declare @size as varchar(2)
set @color='红'
set @size='35'
;
with c_color as--定义一个公用表存放field为‘颜色’的信息
(
select field as color ,value as corvalue,product_id from product where field='颜色'
),
c_size as--再定义一个公用表存放以field为‘尺寸’的信息
(
select field as size,value as sizevalue,product_id from product where not field='颜色'
)
select c2.product_id,color,corvalue,size ,sizevalue from c_color as c1 inner join c_size as c2
on c1.product_id=c2.product_id--两个表求笛卡尔积,可以得出同一个ID所匹配的所有颜色和尺寸
where corvalue=@color and sizevalue=@size--根据输入的颜色尺寸求出id号
/*product_id color corvalue size sizevalue
----------- ----- -------- ---- ---------
1 颜色 红 尺码 35
2 颜色 红 尺码 35
(2 行受影响)
*/
go
drop table product
SELECT distinct(p.product_id),p.product_name,p.special_price,p.price,p.image_default,p.images,p.image_colors
FROM `category_product` `cp`,`attribute` `a` ,`product` `p`
WHERE cp.category_id = 50008899 AND cp.product_id = p.product_id
AND a.type_id = p.product_id AND a.vid in(27370,28314) AND p.status = 1 AND p.stocks > 0
ORDER BY `sort` DESC LIMIT 0,20
create table tb(field nvarchar(10),value nvarchar(10),product_id int)
insert into tb select '颜色','红',1
insert into tb select '颜色','黄',1
insert into tb select '颜色','红',2
insert into tb select '尺码','35',1
insert into tb select '尺码','36',1
insert into tb select '尺码','37',1
insert into tb select '尺码','35',2
insert into tb select '尺码','36',2
go
select distinct a.product_id
from tb a inner join tb b on a.product_id=b.product_id
where a.field='颜色' and a.value='红' and b.field='尺码' and b.value in('37','35')
/*
product_id
-----------
1
2
(2 行受影响)
*/
go
drop table tb
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-09-26 09:29:47
-- Verstion:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([field] varchar(4),[value] varchar(2),[product_id] int)
insert [tb]
select '颜色','红',1 union all
select '颜色','黄',1 union all
select '颜色','红',2 union all
select '尺码','35',1 union all
select '尺码','36',1 union all
select '尺码','37',1 union all
select '尺码','35',2 union all
select '尺码','36',2
--------------开始查询--------------------------
declare @value varchar(10)
set @value='37'
select
a.field,a.value,b.field,b.value,b.product_id
from
tb a join tb b on a.product_id=b.product_id
where
a.field='颜色' and a.value='红' and b.field='尺码' and b.value=@value
----------------结果----------------------------
/*field value field value product_id
----- ----- ----- ----- -----------
颜色 红 尺码 37 1
(1 行受影响)
*/
create table tb(field nvarchar(10),value nvarchar(10),product_id int)
insert into tb select '颜色','红',1
insert into tb select '颜色','黄',1
insert into tb select '颜色','红',2
insert into tb select '尺码','35',1
insert into tb select '尺码','36',1
insert into tb select '尺码','37',1
insert into tb select '尺码','35',2
insert into tb select '尺码','36',2
go
select a.product_id,a.field,a.value,b.field,b.value
from tb a inner join tb b on a.product_id=b.product_id
where a.field='颜色' and a.value='红' and b.field='尺码' and b.value in('37','35')
/*
product_id field value field value
----------- ---------- ---------- ---------- ----------
1 颜色 红 尺码 35
1 颜色 红 尺码 37
2 颜色 红 尺码 35
(3 行受影响)
*/
go
drop table tb
select a.field,a.value,b.field,b.value
from tb a inner join tb b on a.product_id=b.product_id
where a.field='颜色' and a.value='红' and b.field='尺码' and b.value in('37','35')