DECLARE @SQLString varchar(8000)
DECLARE Cursor_A CURSOR FOR SELECT 客户状态 FROM CustomerStatus
SET @SQLString = 'SELECT COUNT(*) AS 客户总数'
DECLARE @StatNamea varchar(100)
OPEN Cursor_A FETCH Next FROM Cursor_A INTO @StatNamea
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = @SQLString + ','
SET @SQLString = @SQLString + '(select count(客户状态) from CustomerContactHistory where 客户状态='''+@StatNamea+''' and OurEmployeeID=3) as '+@StatNamea
FETCH Next FROM Cursor_A INTO @StatNamea
END
CLOSE Cursor_A
DEALLOCATE Cursor_ASET @SQLString = @SQLString + 'FROM CustomerInformation WHERE (客户删除 = 0) AND (OurEmployeeID = 3)'EXECUTE (@SQLString)
DECLARE Cursor_A CURSOR FOR SELECT 客户状态 FROM CustomerStatus
SET @SQLString = 'SELECT COUNT(*) AS 客户总数'
DECLARE @StatNamea varchar(100)
OPEN Cursor_A FETCH Next FROM Cursor_A INTO @StatNamea
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = @SQLString + ','
SET @SQLString = @SQLString + '(select count(客户状态) from CustomerContactHistory where 客户状态='''+@StatNamea+''' and OurEmployeeID=3) as '+@StatNamea
FETCH Next FROM Cursor_A INTO @StatNamea
END
CLOSE Cursor_A
DEALLOCATE Cursor_ASET @SQLString = @SQLString + 'FROM CustomerInformation WHERE (客户删除 = 0) AND (OurEmployeeID = 3)'EXECUTE (@SQLString)
解决方案 »
- 求一个分页sql语句,但是有些恶心的要求.
- SQL语句"去除迪卡尔积分"
- 怎样阻止SQL综合小工具等接入SQL SERVER 2005
- 嵌入式sql如何应用
- 查询分析器的编辑器,tab键默认是 8 个空格,大家喜欢改成 4 个吗?我感觉8个不好看,大家以为呢?
- sqlserver2005/2008 有没有类似oracle 的 /*+ append 或者nolog 机制
- mssql server 20000-出于安全原因,已用该注释替换此文本
- 哪位朋友给一个SQL语句。
- SqlServer2000,插入一条记录后.如何得到刚插入记录的Id?
- 请问:SQL SERVER 7.0有中文版的吗?
- SQL Server + OLEDB/ODBC连接的奇怪问题
- 高难度SQL语句求助
--错误
declare @name varchar;
execute ('select @name = null')
execute内部的变量与外部声明的变量没关系
--正确
declare @name varchar(100);
select @name = 'a'
execute ('declare @name varchar(100);select @name = ''b'';print @name')
/*
运行结果
b
*/
DECLARE Cursor_A CURSOR FOR SELECT 客户状态 FROM CustomerStatus
SET @SQLString = 'SELECT COUNT(*) AS 客户总数'
DECLARE @StatNamea varchar(100)
OPEN Cursor_A FETCH Next FROM Cursor_A INTO @StatNamea
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = @SQLString + ','
SET @SQLString = @SQLString + '(select count(客户状态) from CustomerContactHistory where 客户状态='''+@StatNamea+''' and OurEmployeeID=3) as '+@StatNamea
FETCH Next FROM Cursor_A INTO @StatNamea
END
CLOSE Cursor_A
DEALLOCATE Cursor_ASET @SQLString = @SQLString + 'FROM CustomerInformation WHERE (客户删除 = 0) AND (OurEmployeeID = 3)'EXECUTE (@SQLString)
---------------------------------------------------------------------------
--生成测试数据
create table #a(aid int,名称 varchar(20))
insert into #a select 1,'重要'
insert into #a select 2,'不重要'
insert into #a select 3,'狠不重要'
create table #b(OurID int,名称 varchar(20),aid int)
insert into #b select 3,'重要',1
insert into #b select 3,'不重要',2
insert into #b select 2,null,null
insert into #b select 3,null,null
insert into #b select 3,'狠不重要',3
--执行动态交叉表查询
declare @s varchar(8000)
set @s = 'select count(*) as 客户总数'
select
@s = @s+',['+名称+']=sum(case 名称 when '''+名称+''' then 1 else 0 end)'
from
#a
order by
aid
set @s = @s + ' from #b'
exec(@s)
--输出结果
/*
客户总数 重要 不重要 狠不重要
-------- -------- -------- --------
5 1 1 1
*/
--删除测试数据
drop table #a,#b
go
SET @SQLString = @SQLString + '(select count(客户状态) from CustomerContactHistory where 客户状态=@StatNamea and OurEmployeeID=3)as @StatNamea'--
这句有问题啊!--
SET @SQLString = @SQLString + '(select count(客户状态) from CustomerContactHistory where 客户状态='+@StatNamea +'and OurEmployeeID=3)'--as列名为@StatNamea ,好像怎么用也有问题,这个不知道怎么改