日期:2014-05-16 浏览次数:20553 次
SELECT
u.username,
u.userid,
u.province,
u.dotadmin,
t.tickets,
u.viplevel,
u.regdata,
u.maintainlevel,
d.staffname,
d.staffid
FROM subsidiaryreportsystem.dbo.distributorstaffmapping d WITH (NOLOCK)
LEFT JOIN subsidiaryreportsystem.dbo.tbluser u WITH (NOLOCK)
ON d.userid = u.userid
LEFT JOIN
(
SELECT userid, sum (ticketnums) tickets
FROM subsidiaryreportsystem.dbo.ticketmonitor WITH (NOLOCK)
WHERE date > '2014-2-1 0:00:00' AND date < '2014-2-28 23:59:59'
GROUP BY userid
) t
ON d.userid = t.userid
LEFT JOIN subsidiaryreportsystem.dbo.tblspecialuser s WITH (NOLOCK)
ON s.userid = d.userid
WHERE s.userid IS NULL
AND u.admin = 1
AND d.staffid = 318924
AND u.province = '江苏'
ORDER BY tickets DESC
SELECT TOP 100
u.username,
u.userid,
u.province,
u.dotadmin,
t.tickets,
u.viplevel,
u.regdata,
u.maintainlevel,
d.staffname,
d.staffid
from
(
select
userid,
staffname,
staffid
FROM
subsidiaryreportsystem.dbo.distributorstaffmapping WITH (NOLOCK)
where
staffid = 318924
) d
inner join
(
select
username,
userid,
province,
dotadmin,
viplevel,
regdata,
maintainlevel
from
subsidiaryreportsystem.dbo.tbluser WITH (NOLOCK)
where
admin = 1
AND province = '江苏'
) u
on d.userid=u.userid
left join
(
SELECT
userid,
sum (ticketnums) tickets
FROM
subsidiaryreportsystem.dbo.ticketmonitor WITH (NOLOCK)
WHERE
date > '2014-02-01 0:00:00' AND date < '2014-02-28 23:59:59'
GROUP BY userid
) t
ON d.userid = t.userid
order by tickets