求sql 语句一条解决方案

2012-06-19

求sql 语句一条SQL codecreate table [tb]([商品名] varchar(43),[特性名] varchar(30),[特性值] varchar(

求sql 语句一条

SQL code

create table [tb]([商品名] varchar(43),[特性名] varchar(30),[特性值] varchar(40) ,CONSTRAINT [PK_SM_KeyValue] PRIMARY KEY  NONCLUSTERED     (        [商品名] ,[特性名]    )  ON [PRIMARY]   )insert [tb]select '金士顿内存re5211' ,'大小' ,'1g'  union allselect '金士顿内存re5211' ,'品牌' ,'金士顿'  union allselect '金士顿内存re5211' ,'类型' ,'服务器内存'  union allselect '金士顿内存te5311' ,'大小' ,'4g'  union allselect '金士顿内存te5311' ,'品牌' ,'金士顿'  union allselect '金士顿内存te5311' ,'类型' ,'笔记本内存'  union allselect 'sandisk内存te5311' ,'大小' ,'4g'  union allselect 'sandisk内存te5311' ,'品牌' ,'sandisk'  union allselect 'sandisk内存te5311' ,'类型' ,'笔记本内存'  union allselect 'pentium cpu e2500' ,'主频' ,'2.18'  union allselect 'pentium cpu e2500' ,'接口' ,'socket 744'  union allselect 'pentium cpu T2500' ,'主频' ,'2.38'  union allselect 'pentium cpu T2500' ,'接口' ,'socket 744'

查询大小是4g的内存

查询大小是4g ，品牌是 sandisk的内存

[解决办法]

SQL code

select  * from tb where  [特性值]='4g' and  charindex('sandisk',[商品名])<>0
[解决办法]
SQL codeselect  * from tb where [特性名]='大小' AND  [特性值]='4g'
[解决办法]
SQL code--查询大小是4g的内存SELECT [商品名]FROM tbWHERE [特性名] = '大小' AND [特性值] = '4G'--查询大小是4g ，品牌是 sandisk的内存SELECT [商品名]FROM tbWHERE [特性名] = '大小' AND [特性值] = '4G'INTERSECTSELECT [商品名]FROM tbWHERE [特性名] = '品牌' AND [特性值] = 'sandisk'
[解决办法]
SQL codecreate table [tb]([商品名] varchar(43),[特性名] varchar(30),[特性值] varchar(40) ,CONSTRAINT [PK_SM_KeyValue] PRIMARY KEY  NONCLUSTERED     (        [商品名] ,[特性名]    )  ON [PRIMARY]   )insert [tb]select '金士顿内存re5211' ,'大小' ,'1g'  union allselect '金士顿内存re5211' ,'品牌' ,'金士顿'  union allselect '金士顿内存re5211' ,'类型' ,'服务器内存'  union allselect '金士顿内存te5311' ,'大小' ,'4g'  union allselect '金士顿内存te5311' ,'品牌' ,'金士顿'  union allselect '金士顿内存te5311' ,'类型' ,'笔记本内存'  union allselect 'sandisk内存te5311' ,'大小' ,'4g'  union allselect 'sandisk内存te5311' ,'品牌' ,'sandisk'  union allselect 'sandisk内存te5311' ,'类型' ,'笔记本内存'  union allselect 'pentium cpu e2500' ,'主频' ,'2.18'  union allselect 'pentium cpu e2500' ,'接口' ,'socket 744'  union allselect 'pentium cpu T2500' ,'主频' ,'2.38'  union allselect 'pentium cpu T2500' ,'接口' ,'socket 744'  --1select * from tb where [特性名]='大小' and [特性值]='4g'--2select * from tb where [特性名]='大小' and [特性值]='4g'and [商品名]=(select [商品名] from tb where [特性名]='品牌' and [特性值]='sandisk')
[解决办法]
说明一下:

1. 提取所有满足任意条件的数据SQL codeinsert into @resultselect a.*from [tb] ajoin @filter b on 1=1    and a.特性名 = b.特性名    and a.特性值 = b.特性值

热点排行

SQL Server

求sql 语句一条解决方案