我想對 SQL 表中的資料進行計數Archive_Hits并創建一個programStarts顯示該資料的列。然后我想取該programStarts列并將其與另一個 Select 陳述句組合以顯示一個資料表。
這是我的程式：
ALTER PROCEDURE [dbo].[testing2] @startdate datetime, @enddate datetime
AS
BEGIN
SELECT Archive_Hits.inst_id, Count(Archive_Hits.type) AS programStarts
FROM Archive_Hits
WHERE Archive_Hits.type<>'New' AND Archive_Hits.accessed_on BETWEEN cast ( @startdate as date) and cast ( @enddate as date)
GROUP BY Archive_Hits.inst_id
SELECT email,product_market,install_code, programStarts
FROM (Archive_ProgUsers INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id)
INNER JOIN Archive_Hits AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC
請注意，我嘗試使用 UNION 和 UNION ALL 方法，但如果每個陳述句中的列數不同，這將不起作用。
此外，第二個 select 陳述句回傳錯誤，因為它無法識別第一個 Select 陳述句中新創建的列。
============================== 這是一個示例結果表結果
uj5u.com熱心網友回復：
您需要提供示例資料，但我認為這是您需要的：
SELECT
email,
product_market,
install_code,
Count(case when hitsCount.type <> 'New' and hitsCount.accessed_on BETWEEN cast(@startdate as date) and cast(@enddate as date) then 1 end) over (partition by inst_id) as programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN Archive_Hits AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC
好的，那么你需要這個：
SELECT
email,
product_market,
install_code,
programStarts
FROM Archive_ProgUsers
INNER JOIN Archive_Installations ON Archive_ProgUsers.id = Archive_Installations.user_id
INNER JOIN (
SELECT inst_id
, Count(case when hitsCount.type <> 'New' and hitsCount.accessed_on BETWEEN cast(@startdate as date) and cast(@enddate as date) then 1 end) as programStarts
From Archive_Hits
GROUP BY Archive_Hits.inst_id
) AS hitsCount ON hitsCount.inst_id = Archive_Installations.id
ORDER BY programStarts DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/325096.html
標籤：sql asp.net sql-server 存储过程
上一篇：如何使用SQL將這四行轉換為列
下一篇：未找到解決方案的sql案例