如何在storeprocedure中为给定的表生成SQL查询？ [英] how to generate SQL query for given table in storeprocedure ?

问题描述

我有两张桌子：

1）客户

字段是：



client_id（主键）

client_name

city_id（外键）



2）city

字段是：



city_id（主键）

city_name



我想要输出：在城市（例如：艾哈迈达巴德）客户端数量（例如：35）

城市& 客户端是gridview中的列名。

I have two table:
1)client
fields are:

client_id(primary key)
client_name
city_id(foreign key)

2)city
fields are:

city_id(primary key)
city_name

I want output :in city(Ex: ahmedabad) number of client (Ex:35)
city & no of client are column name in gridview.

推荐答案

create Procedure sp_cityClient

（@city_id int，< br $>
@procedureType char（1）

）

as

开始

如果@ ProcedureType =''S''

begin

从City选择city.city_name，count（client.client_id）

客户端上的left join客户端.city_id = city.city_id

where city.city_id=@city_id

end

end

create Procedure sp_cityClient
(@city_id int,
@procedureType char(1)
)
as
begin
if @ProcedureType=''S''
begin
select city.city_name, count(client.client_id) from City
left join client on client.city_id=city.city_id
where city.city_id=@city_id
end
end

CREATE TABLE [dbo].[T_Client](
    [client_id] [int] NULL,
    [client_name] [varchar](50) NULL,
    [city_id] [int] NULL
)

CREATE TABLE [dbo].[T_City](
	[city_id] [int] NULL,
	[city_name] [varchar](50) NULL
)

select T_City.city_name,count(T_Client.client_name) Total_Client
from T_City
inner join T_Client
on T_City.city_id=T_Client.city_id
group by T_City.city_name

这篇关于如何在storeprocedure中为给定的表生成SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！