Demo entry 6778040

查询sql

   

Submitted by anonymous on Dec 12, 2018 at 08:38
Language: SQL. Code size: 1.8 kB.

SELECT
fin.Base_GroupID,
	fin.Base_GroupName,
	SUM (fin.o) line
FROM
	(
		SELECT
m2.Base_GroupID,
	m2.Base_GroupName,
	CASE
WHEN ISNULL(m2.inn, 0) > ISNULL(m2.outn, 0) THEN
	1
ELSE
	0
END o
FROM
	(
		SELECT
			m1.Base_GroupID,
			m1.Base_PerID,
			m1.Base_PerName,
			m1.Base_CardNo,
			m1.Base_GroupName,
			MAX (m1.inn) inn,
			MAX (m1.outn) outn
		FROM
			(
				SELECT
					m.Base_GroupID,
					m.Base_PerID,
					m.Base_PerName,
					m.Base_CardNo,
					m.Base_GroupName,
					CASE m.Device_ID
				WHEN 128 THEN
					tt
				ELSE
					NULL
				END inn,
				CASE m.Device_ID
			WHEN 129 THEN
				tt
			ELSE
				NULL
			END outn
			FROM
				(
					SELECT
						p.Base_GroupID,
						p.Base_PerID,
						p.Base_PerName,
						p.Base_CardNo,
						Base_GroupName,
						dev.Device_ID,
						MAX (
							CONVERT (
								VARCHAR (20),
								l.Access_DateTime,
								120
							)
						) tt
					FROM
						General_Group g
					INNER JOIN General_Personnel p ON p.Base_GroupID = g.Base_GroupID
					LEFT JOIN AcvB_AccessLog l ON p.Base_PerID = l.Base_PerID
					LEFT JOIN AcvB_Device dev ON l.Device_ID = dev.Device_ID
					WHERE
						p.Base_IsDel = 0
					AND g.Base_IsDel = 0
					AND dev.Device_ID IN (128, 129)
					AND l.Open_Flag = 16
					AND l.Access_DateTime > dateadd(DAY ,- 100, getdate())
					GROUP BY
						p.Base_GroupID,
						p.Base_PerID,
						p.Base_PerName,
						p.Base_CardNo,
						g.Base_GroupName,
						dev.Device_ID
				) m
			) m1
		GROUP BY
			m1.Base_GroupID,
			m1.Base_PerID,
			m1.Base_PerName,
			m1.Base_CardNo,
			m1.Base_GroupName
	) m2
	) fin
GROUP BY
fin.Base_GroupID,
	fin.Base_GroupName
ORDER BY fin.Base_GroupID

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).