select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID,
(select count(*) from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as CountSameNum
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductIDwhere WebSiteHostID=1
group by a.WebSiteHostID,b.ADProductID
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID,
(select count(*) from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as CountSameNum
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductIDwhere WebSiteHostID=1
group by a.WebSiteHostID,b.ADProductID
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) t
group by WebSiteHostID WebUserName ADProductID
WebSiteHostID WebUserName ADProductID CountSameNum
1 aaa 9 1
1 aaa 10 1
--------------------------------------------------------------第一行 CountSameNum 应该是 2 啊...2 楼的大大 ............额,反正不对...
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID,
(select count(*) from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID and ADProductID=b.ADProductID) as CountSameNum
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID where WebSiteHostID=1
group by a.WebSiteHostID,b.ADProductID这样呢?
DATA AS(
select
a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
),
RE1 AS(
-- 去除相同的 ADProductID (楼主的意思没太看懂, 因为不知道 ADProductID 之外其他列如何处理)
SELECT DISTINCT
WebSiteHostID, WebUserName, ADProductID
FROM DATA
)
SELECT
*
FROM RE1
CROSS APPLY(
-- Count() 相同的 ADProductID
SELECT CountSameNum = COUNT(*)
FROM DATA
WHERE ADProductID = RE1.ADProductID
)B
*,
CountSameNum = (
SELECT COUNT(*)
FROM(
select
a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) DATA
WHERE ADProductID = RE1.ADProductID)
FROM(
-- 去除相同的 ADProductID (楼主的意思没太看懂, 因为不知道 ADProductID 之外其他列如何处理)
SELECT DISTINCT
WebSiteHostID, WebUserName, ADProductID
FROM(
select
a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) DATA
)RE1
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=15
)
group by WebSiteHostID WebUserName ADProductID
还是报: 服务器: 消息 156,级别 15,状态 1,行 10
在关键字 'group' 附近有语法错误。
------
对..你连一下试试.我看错了.
WebSiteHostID WebUserName ADProductID CountSameNum
1 aaa 9 0
1 aaa 10 0
--------------------------------------------------------------
我直接复制你结果的字段名,之间忘了加逗号了,抱歉:select WebSiteHostID WebUserName ADProductID, CountSameNum=count(1)
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) t
group by WebSiteHostID,WebUserName,ADProductID
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) t
group by WebSiteHostID,WebUserName,ADProductID
from
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1
) t
group by WebSiteHostID,WebUserName,ADProductID小楼少了一个from
(select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
where WebSiteHostID=1)a
group by WebSiteHostID,WebUserName,ADProductID
1 aaa 9 2 4.0000 8.0000
1 aaa 10 1 5.0000 5.0000----------------------------------------------------------------------------- 现在 邹建大大... 给的方法 我小改下:SELECT
*,
CountSameNum = (
SELECT COUNT(*)
FROM(
select
a.WebSiteHostID,
c.OutPrice, -- 加个字段
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID --加了张表
where WebSiteHostID=15
) DATA
WHERE ADProductID = RE1.ADProductID)
FROM(
SELECT DISTINCT
WebSiteHostID, WebUserName, ADProductID,OutPrice
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
)RE1实现了:
-------------------------------------------------------------------------
WebSiteHostID WebUserName ADProductID CountSameNum OutPrice
1 aaa 9 2 4.0000
1 aaa 10 1 5.0000 ----------------------------------------------------------------------------- 但关键还是在求和啊.... 只要OutPrice*CountSameNum 就可以了。 本以为 加个字段 求和很简单...但现在 牛人写的东西,偶不知道怎么用 CountSameNum 了。帮看看啊...真是.....失败。
select WebSiteHostID,WebUserName,ADProductID,CountSameNum=count(1),OutPrice,c.OutPrice*CountSameNum as TotalPrice
from
(
select a.WebSiteHostID,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID,
c.OutPrice
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) t
group by WebSiteHostID,WebUserName,ADProductID
这个样子不好使啊...
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID into #T
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductIDwhere WebSiteHostID=1select WebsiteHostID,WebUserName,ADProductID,count(AdProductID) as CountSameNum from #T
group by WebsiteHostID,WebUserName,ADProductID
WebDmainNameID ADProductID OutPrice
1 9 4.0000
2 9 6.0000
3 10 5.0000
而WebSiteHostID表可能是这样的:
WebSiteHostID WebDomainNameID ADProductID
1 1 9
1 2 9
1 3 10
可能无法根据你提供的那个连接语句再求总的 TotalPrice,因为,相同的ADProductID可能有不同的OutPrice.
SELECT
*,
CountSameNum = (
SELECT COUNT(*)
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
WHERE ADProductID = RE1.ADProductID),
CountSameNum*OutPrice as TotalPrice --列名 'CountSameNum' 无效。
FROM(
SELECT DISTINCT
WebSiteHostID, WebUserName, ADProductID,OutPrice
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
)RE1
不应该啊....这是怎么回事啊????????????
SELECT
*,
CountSameNum = (
SELECT COUNT(*)
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
WHERE ADProductID = RE1.ADProductID),
TotalPrice = (
SELECT COUNT(*)
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
WHERE ADProductID = RE1.ADProductID)*OutPrice
--CountSameNum*OutPrice as TotalPrice
--OutPrice*CountSameNu as TotalPrice
FROM(
SELECT DISTINCT
WebSiteHostID, WebUserName, ADProductID,OutPrice
FROM(
select
a.WebSiteHostID,
c.OutPrice,
(select WebUserName from WebSiteHost where WebSiteHost.WebSiteHostID=a.WebSiteHostID) as WebUserName,
b.ADProductID
from WebSiteHostOrders a
inner join GetProducts b on a.WebDomainNameID= b.WebDomainNameID and a.ADProductID=b.ADProductID
inner join ADProduct c on a.ADProductID=c.ADProductID
where WebSiteHostID=15
) DATA
)RE1