沒測試過可以試試看
子查詢
SELECT SUM(cmid)
FROM (SELECT COUNT( DISTINCT userid ) AS hits, cmid, module
FROM mdl_log
WHERE course = 39
GROUP BY module
LIMIT 0 , 30)
WHERE module IN ('course','page','resource')
稍作修改如下,可以執行, tks
SELECT SUM(cmid) AS total
FROM (SELECT COUNT( DISTINCT userid ) AS hits, cmid, module
FROM mdl_log as t
WHERE course = 39
GROUP BY module) t1
WHERE module IN ('course','page','resource')
這樣比較好看~!
SELECT SUM(cmid) AS total
FROM (SELECT COUNT( DISTINCT userid ) AS hits
, cmid
, module
FROM mdl_log as t
WHERE course = 39
GROUP BY module) t1
WHERE module IN ('course','page','resource')
感覺直接跟你說解答的話，有點壞行情耶，要不要自己再多花個把月想一想。多踹踹，的確有兩個select 不過放哪呢?呵呵
請教前輩
是下列的應用嗎？但目前子查詢OK，我把它併在一塊就有問題了，再請指點迷津，謝謝
Select sum(tem.temp) from (select cmid, module
,COUNT(DISTINCT userid) as temp
from mdl_log group by module HAVING module in ('page','course'));
提示1：你會需要兩個定義 提示2：不擺在那 提示3：不會那麼短
是類似下列語法嗎？ (雖然下列語法一樣有問題)，並請教為什麼不能這樣使用sum(COUNT(userid)), 感謝
SELECT COUNT(userid) AS hits
FROM mdl_log
WHERE course =39 join (
select count(userid),module as hits from mdl_log where module in ('course','page','resource') and course =39
GROUP BY module
) t1 using (module,hits)
group by module
因為原來你的語法的主要是拿來改變原始表單一次的，你已經改變一次過了。你想加入另一次改變的改變，怎麼加都會加不進。上面語法，以100分計算，+2-1.8=0.2，進步0.2....不過這語法進是或許可以進但是看不出(指定)module是擺哪去了???你不是要指定module，把對應cmid顯示出來嗎?還是你只是要隨便三個的東西而不用(對應)?
如果不兩個select....你有沒有試過在後面加, case module when "course" then cmid when "folder" then cmid when "page" then cmid else null end
as addon
這樣 是你想要的???
iThome鐵人賽
看影片追技術