sql if，case when,stuff 使用

最新推荐文章于 2025-06-18 18:05:39 发布

原创最新推荐文章于 2025-06-18 18:05:39 发布 · 415 阅读

0 ·

CC 4.0 BY-SA版权

博客给出一段SQL代码，用于根据店铺ID和关键词查询商品信息。代码根据basicset表的查询结果分两种情况执行不同的查询逻辑，还展示了使用SqlCommand和SqlParameter执行SQL语句的代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

string strSql = @"if exists(select * from basicset where shopid=@shopid and usesubtype=1)
begin
select distinct a.item_no 商品编号,a.item_subno 商品自编码,a.item_name 商品名称,a.item_price 价格,a.item_spec 规格, a.unit_id 多规格编号,
stuff((select ','+branch_no from t_branch_info where shopid=a.shopid and stop_flag ='1' and
branch_no not in(select branch_no from t_branch_item where shopid=a.shopid and productid =a.Id) for xml path('')), 1, 1, '') as 售卖门店
,stuff((select ','+branch_no from t_branch_item where shopid=a.shopid and productid =a.Id for xml path('')), 1, 1, '') as 未售卖门店
,case when c.type_name is null then b.type_name else c.type_name+'--'+b.type_name end 类别
from t_item_info a
left join t_item_type b on b.shopid=a.shopid and b.type_no = a.type_no and b.useflag ='1'
left join t_item_type c on c.shopid=a.shopid and c.parentno = a.type_no and c.useflag ='1'
where a.shopid=@shopid and a.useflag ='1' and a.item_name like @keyword
end
else
begin
select distinct a.item_no 商品编号,a.item_subno 商品自编码,a.item_name 商品名称,a.item_price 价格,a.item_spec 规格,a.unit_id 多规格编号,
stuff((select ','+branch_no from t_branch_info where shopid=a.shopid and stop_flag ='1' and
branch_no not in(select branch_no from t_branch_item where shopid=a.shopid and productid =a.Id) for xml path('')), 1, 1, '') as 售卖门店
,stuff((select ','+branch_no from t_branch_item where shopid=a.shopid and productid =a.Id for xml path('')), 1, 1, '') as 未售卖门店
,b.type_name 类别
from t_item_info a
left join t_item_type b on b.shopid=a.shopid and b.type_no = a.type_no and b.useflag ='1'
where a.shopid=@shopid and a.useflag ='1' and a.item_name like @keyword
end";
SqlCommand sqlCmd = new SqlCommand(strSql, connection);
var parameter = new SqlParameter[]{
new SqlParameter("@shopid",shopid),
new SqlParameter("@keyword","%"+keyword+"%")
};