再问〈求一个sql语句写法〉

2007-04-11 02:29:51 275 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

保姆级教程：Halcon 3D点云匹配与差异分析避坑指南（从采样到可视化）本文提供了一份详细的Halcon 3D点云匹配与差异分析实战指南，涵盖从模型准备到可视化的全流程。重点解析了STL文件读取单位误区、点云采样密度优化、表面模型创建技巧以及差异分析的专业手法，帮助用户避开常见陷阱，提升工业检测效率与精度。阅读详情

A表：
cardtypeid,date_time
1 2007/02/03
2 2007/03/03
3 2007/04/03
2 2007/04/03
B表：
cardtypeid, cardtypeName
1 甲
2 乙
3 丙

查询结果：
一，按照月份统计
月份甲乙丙合计
2007/02 1 0 0 1
2007/03 0 1 0 1
2007/04 0 1 1 2
合计 1 2 1 4

二，按照日期统计
日期甲乙丙合计
2007/02/03 1 0 0 1
2007/03/03 0 1 0 1
2007/04/03 0 1 1 2
合计 1 2 1 4

三，按照星期统计（2007/02/03和2007/03/03都是星期二，2007/04/03是星期六）
星期甲乙丙合计
星期二 1 1 0 2
星期六 0 1 1 2
合计

潇洒老乌龟大哥的写法如下，
但是我想成存储过程在前端调用时
会报错，“拒绝了对对象.....的select 权限”，
这个时候就必须在数据库安全设置里对此用户设置“db_owner”的权限。
有没有其他的写法可以避免此情况？（不带exec(@sql)命令的）
————————————————————————————————————
declare @sql varchar(8000)
set @sql = 'select isnull(月份,''合计'') 月份'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 月份 with rollup'
exec(@sql)

...全文

【亲测有效】OpenClaw Gateway 出现 “(no output)“ 卡死问题完整解决指南：从手动重启到 systemd Watchdog 自动恢复最近在使用 OpenClaw（自托管 AI 助手框架，前身 Clawdbot/Moltbot）时，遇到了一个非常常见的顽疾：TUI（终端界面）或 Telegram 等通道经常显示 "(no output)"，消息发出去后机器人完全没反应，看起来像是卡死了。阅读详情

treeway

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

paoluo 2007-04-11

打赏
举报

--如果你的B表中的cardtypeName是固定的，就可以不用寫成存儲過程，不用EXEC，否則就要用EXEC

--一，按照月份统计
Select
IsNull(月份, '合计') As 月份, 甲, 乙, 丙, 合计
From
(
Select
Convert(Varchar(7), date_time, 111) As 月份,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
Convert(Varchar(7), date_time, 111)
With Rollup
) A

treeway 2007-04-11

打赏
举报

写成存储过程在前端调用时
会报错，“拒绝了对对象.....的select 权限”，
这个时候就必须在数据库安全设置里对此用户设置“db_owner”的权限。
有没有其他的写法可以避免此情况？（不带exec(@sql)命令的）

dawugui 2007-04-11

打赏
举报

if object_id('pubs..A') is not null
drop table A
go
create table A(cardtypeid int,date_time datetime)
insert into A(cardtypeid,date_time) values(1,'2007/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if object_id('pubs..B') is not null
drop table B
go
create table B(cardtypeid int, cardtypeName varchar(10))
insert into B(cardtypeid, cardtypeName) values(1,'甲')
insert into B(cardtypeid, cardtypeName) values(2,'乙')
insert into B(cardtypeid, cardtypeName) values(3,'丙')
go

declare @sql varchar(8000)
set @sql = 'select isnull(星期,''合计'') 星期'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , datename(weekday,a.date_time) 星期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , datename(weekday,a.date_time) 星期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 星期 with rollup'
exec(@sql)

drop table A,B

/*
星期丙甲乙合计
------------------------------ ----------- ----------- ----------- -----------
星期二 1 0 1 2
星期六 0 1 1 2
合计 1 1 2 4
（所影响的行数为 1 行）
*/

dawugui 2007-04-11

打赏
举报

if object_id('pubs..A') is not null
drop table A
go
create table A(cardtypeid int,date_time datetime)
insert into A(cardtypeid,date_time) values(1,'2007/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if object_id('pubs..B') is not null
drop table B
go
create table B(cardtypeid int, cardtypeName varchar(10))
insert into B(cardtypeid, cardtypeName) values(1,'甲')
insert into B(cardtypeid, cardtypeName) values(2,'乙')
insert into B(cardtypeid, cardtypeName) values(3,'丙')
go

declare @sql varchar(8000)
set @sql = 'select isnull(日期,''合计'') 日期'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , convert(varchar(10),a.date_time,120) 日期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , convert(varchar(10),a.date_time,120) 日期 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 日期 with rollup'
exec(@sql)

drop table A,B

/*
日期丙甲乙合计
---------- ----------- ----------- ----------- -----------
2007-02-03 0 1 0 1
2007-03-03 0 0 1 1
2007-04-03 1 0 1 2
合计 1 1 2 4

*/

dawugui 2007-04-11

打赏
举报

if object_id('pubs..A') is not null
drop table A
go
create table A(cardtypeid int,date_time datetime)
insert into A(cardtypeid,date_time) values(1,'2007/02/03')
insert into A(cardtypeid,date_time) values(2,'2007/03/03')
insert into A(cardtypeid,date_time) values(3,'2007/04/03')
insert into A(cardtypeid,date_time) values(2,'2007/04/03')
go

if object_id('pubs..B') is not null
drop table B
go
create table B(cardtypeid int, cardtypeName varchar(10))
insert into B(cardtypeid, cardtypeName) values(1,'甲')
insert into B(cardtypeid, cardtypeName) values(2,'乙')
insert into B(cardtypeid, cardtypeName) values(3,'丙')
go

declare @sql varchar(8000)
set @sql = 'select isnull(月份,''合计'') 月份'
select @sql = @sql + ' , sum(case cardtypeName when ''' + cardtypeName + ''' then 1 else 0 end) [' + cardtypeName + ']'
from (select distinct cardtypeName from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t) as m
set @sql = @sql + ' ,count(*) as 合计 from (select a.cardtypeid , convert(varchar(7),a.date_time,120) 月份 , b.cardtypeName from A,B where a.cardtypeid = b.cardtypeid) t group by 月份 with rollup'
exec(@sql)

drop table A,B

/*
月份丙甲乙合计
------- ----------- ----------- ----------- -----------
2007-02 0 1 0 1
2007-03 0 0 1 1
2007-04 1 0 1 2
合计 1 1 2 4
*/

paoluo 2007-04-11

打赏
举报

--如果你的B表中的cardtypeName不是固定的，只能用EXEC，以下為三種情況動態的寫法

--建立測試環境
Create Table A
(cardtypeid Int,
date_time DateTime)
Insert A Select 1, '2007/02/03'
Union All Select 2, '2007/03/03'
Union All Select 3, '2007/04/03'
Union All Select 2, '2007/04/03'
Create Table B
(cardtypeid Int,
cardtypeName NVarchar(10))
Insert B Select 1, N'甲'
Union All Select 2, N'乙'
Union All Select 3, N'丙'
GO
--建立存儲過程
--一，按照月份统计
Create Procedure SP_MonthReport
As
Begin
Declare @S NVarchar(4000)
Select @S = N' Select IsNull(月份,''合计'') As 月份'
Select @S = @S + ', SUM(Case cardtypeName When N''' + cardtypeName + ''' Then 1 Else 0 End) As [' + cardtypeName + ']'
From B Group By cardtypeName Order By Min(cardtypeid)
Select @S = @S + N' , Count(*) As 合计 From (Select A.cardtypeid, Convert(Varchar(7), A.date_time, 111) As 月份, B.cardtypeName From A Inner Join B On A.cardtypeid = B.cardtypeid) C Group By 月份 With Rollup'
EXECUTE sp_executesql @S
End
GO
--二，按照日期统计
Create Procedure SP_DayReport
As
Begin
Declare @S NVarchar(4000)
Select @S = N' Select IsNull(日期,''合计'') As 日期'
Select @S = @S + ', SUM(Case cardtypeName When N''' + cardtypeName + ''' Then 1 Else 0 End) As [' + cardtypeName + ']'
From B Group By cardtypeName Order By Min(cardtypeid)
Select @S = @S + N' , Count(*) As 合计 From (Select A.cardtypeid, Convert(Varchar(10), A.date_time, 111) As 日期, B.cardtypeName From A Inner Join B On A.cardtypeid = B.cardtypeid) C Group By 日期 With Rollup'
EXECUTE sp_executesql @S
End
GO
--三，按照星期统计（2007/02/03和2007/03/03都是星期二，2007/04/03是星期六）
Create Procedure SP_WeekReport
As
Begin
Declare @S NVarchar(4000)
Select @S = N' Select IsNull(星期,''合计'') As 星期'
Select @S = @S + ', SUM(Case cardtypeName When N''' + cardtypeName + ''' Then 1 Else 0 End) As [' + cardtypeName + ']'
From B Group By cardtypeName Order By Min(cardtypeid)
Select @S = @S + N' , Count(*) As 合计 From (Select A.cardtypeid, DateName(dw, date_time) As 星期, B.cardtypeName From A Inner Join B On A.cardtypeid = B.cardtypeid) C Group By 星期 With Rollup'
EXECUTE sp_executesql @S
End
GO
--測試
EXEC SP_MonthReport
EXEC SP_DayReport
EXEC SP_WeekReport
GO
--刪除測試環境
Drop Table A, B
Drop Procedure SP_MonthReport, SP_DayReport, SP_WeekReport
--結果
/*
月份甲乙丙合计
2007/02 1 0 0 1
2007/03 0 1 0 1
2007/04 0 1 1 2
合计 1 2 1 4

日期甲乙丙合计
2007/02/03 1 0 0 1
2007/03/03 0 1 0 1
2007/04/03 0 1 1 2
合计 1 2 1 4

星期甲乙丙合计
星期二 0 1 1 2
星期六 1 1 0 2
合计 1 2 1 4
*/

paoluo 2007-04-11

打赏
举报

--如果你的B表中的cardtypeName是固定的，那麼就可以如下寫，不用EXEC，否則就要用EXEC

--建立測試環境
Create Table A
(cardtypeid Int,
date_time DateTime)
Insert A Select 1, '2007/02/03'
Union All Select 2, '2007/03/03'
Union All Select 3, '2007/04/03'
Union All Select 2, '2007/04/03'
Create Table B
(cardtypeid Int,
cardtypeName Nvarchar(10))
Insert B Select 1, N'甲'
Union All Select 2, N'乙'
Union All Select 3, N'丙'
GO
--測試
--一，按照月份统计
Select
IsNull(月份, '合计') As 月份, 甲, 乙, 丙, 合计
From
(
Select
Convert(Varchar(7), date_time, 111) As 月份,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
Convert(Varchar(7), date_time, 111)
With Rollup
) A

--二，按照日期统计
Select
IsNull(日期, '合计') As 日期, 甲, 乙, 丙, 合计
From
(
Select
Convert(Varchar(10), date_time, 111) As 日期,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
Convert(Varchar(10), date_time, 111)
With Rollup
) A

--三，按照星期统计（2007/02/03和2007/03/03都是星期二，2007/04/03是星期六）
Select
IsNull(星期, '合计') As 星期, 甲, 乙, 丙, 合计
From
(
Select
DateName(dw, date_time) As 星期,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
DateName(dw, date_time)
With Rollup
) A
GO
--刪除測試環境
Drop Table A, B
--結果
/*
月份甲乙丙合计
2007/02 1 0 0 1
2007/03 0 1 0 1
2007/04 0 1 1 2
合计 1 2 1 4

日期甲乙丙合计
2007/02/03 1 0 0 1
2007/03/03 0 1 0 1
2007/04/03 0 1 1 2
合计 1 2 1 4

星期甲乙丙合计
星期二 0 1 1 2
星期六 1 1 0 2
合计 1 2 1 4
*/

lin_now 2007-04-11

打赏
举报

EXECUTE sp_executesql @sql
默认 public权限

paoluo 2007-04-11

打赏
举报

--如果你的B表中的cardtypeName是固定的，那麼就可以如下寫，不用EXEC，否則就要用EXEC
--一，按照月份统计
Select
IsNull(月份, '合计') As 月份, 甲, 乙, 丙, 合计
From
(
Select
Convert(Varchar(7), date_time, 111) As 月份,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
Convert(Varchar(7), date_time, 111)
With Rollup
) A

--二，按照日期统计
Select
IsNull(月份, '合计') As 月份, 甲, 乙, 丙, 合计
From
(
Select
Convert(Varchar(10), date_time, 111) As 月份,
SUM(Case cardtypeName When '甲' Then 1 Else 0 End) As 甲,
SUM(Case cardtypeName When '乙' Then 1 Else 0 End) As 乙,
SUM(Case cardtypeName When '丙' Then 1 Else 0 End) As 丙,
Count(*) As 合计
From
A
Inner Join
B
On A.
cardtypeid = B.cardtypeid
Group By
Convert(Varchar(10), date_time, 111)
With Rollup
) A

再问〈求一个sql语句写法〉

相关推荐