以下sql是有语法错语的,请问有没有类似功能的SQL文,非常感谢!
select a.id, (select count(a.id) from a where a.id=1) as cnt1,(select count(a.id) from a where a.id=2) as cnt2 from T_table a;
select a.id, (select count(a.id) from a where a.id=1) as cnt1,(select count(a.id) from a where a.id=2) as cnt2 from T_table a;
其实只要改一下就行了
select a.id,a1.id as cnt1,a2.id as cnt2 from a,(select count(a.id) from a where a.id=1) a1,(select count(a.id) from a where a.id=2) a2
select a.id,a1.cnt1,a2.cnt2 from a,(select count(a.id) as cnt1 from a where a.id=1) a1,(select count(a.id) as cnt2 from a where a.id=2) a2
select a.id, (select count(id) from T_table where id=1) as cnt1
from T_table a
select a.id,a1.cnt1,a2.cnt2 from a,(select count(a.id) as cnt1 from a where a.id=1) a1,(select count(a.id) as cnt2 from a where a.id=2) a2
换成以下这种句子就不行了,
select a.id,a1.cnt1,a2.cnt2 from (select id from tbl_a)a,(select count(a.id) as cnt1 from a where a.id=1) a1,(select count(a.id) as cnt2 from a where a.id=2) a2
SELECT C.NAME, C.SN,C.INTERNAL_RESOURCE_ID
,LUN_DATA.LUN_TOTAL_CNT,LUN_DATA.LUN_LOW_CNT,LUN_DATA.LUN_MIDDLE_CNT
,FRONTEND_PORT_DATA.FRONTEND_PORT_TOTAL_CNT,FRONTEND_PORT_DATA.FRONTEND_PORT_LOW
_CNT,FRONTEND_PORT_DATA.FRONTEND_PORT_MIDDLE_CNT
,CONTROLLER_DATA.CONTROLLER_TOTAL_CNT,CONTROLLER_DATA.CONTROLLER_LOW_CNT,CONTROL
LER_DATA.CONTROLLER_MIDDLE_CNT
,RAIDGROUP_DATA.RAIDGROUP_TOTAL_CNT,RAIDGROUP_DATA.RAIDGROUP_LOW_CNT,RAIDGROUP_D
ATA.RAIDGROUP_MIDDLE_CNT
,DISK_DATA.DISK_TOTAL_CNT,DISK_DATA.DISK_LOW_CNT,DISK_DATA.DISK_MIDDLE_CNT FROM
TBL_RM_STORAGEARRAY C LEFT JOIN (SELECT COUNT(A.LOW_CNT) AS
LUN_TOTAL_CNT,SUM(A.LOW_CNT) AS LUN_LOW_CNT,SUM(MIDDLE_CNT) AS LUN_MIDDLE_CNT,
ARRAY_RESOURCE_ID FROM (SELECT CASE WHEN PLUN.AVG_UTILIZATION >=0 AND
PLUN.AVG_UTILIZATION <=50 THEN 1 ELSE null END AS LOW_CNT , CASE WHEN
PLUN.AVG_UTILIZATION >50 AND PLUN.AVG_UTILIZATION <=70 THEN 1 ELSE null END AS
MIDDLE_CNT, B.ARRAY_RESOURCE_ID FROM TBL_PM_LUN_DAILY PLUN INNER JOIN
TBL_RM_LUN B ON PLUN.RESOURCE_ID = B.INTERNAL_RESOURCE_ID INNER JOIN
TBL_RM_STORAGEARRAY C ON C.INTERNAL_RESOURCE_ID = B.ARRAY_RESOURCE_ID WHERE
AGGREGATED_TIME >=1330841249657 AND AGGREGATED_TIME <=1330927649657 )A GROUP BY
ARRAY_RESOURCE_ID)LUN_DATA ON C.INTERNAL_RESOURCE_ID
=LUN_DATA.ARRAY_RESOURCE_ID LEFT JOIN (SELECT COUNT(A.LOW_CNT) AS
FRONTEND_PORT_TOTAL_CNT,SUM(A.LOW_CNT) AS FRONTEND_PORT_LOW_CNT,SUM(MIDDLE_CNT)
AS FRONTEND_PORT_MIDDLE_CNT, ARRAY_RESOURCE_ID FROM (SELECT CASE WHEN
PFRONTEND_PORT.AVG_UTILIZATION >=0 AND PFRONTEND_PORT.AVG_UTILIZATION <=50 THEN
1 ELSE null END AS LOW_CNT , CASE WHEN PFRONTEND_PORT.AVG_UTILIZATION >50 AND
PFRONTEND_PORT.AVG_UTILIZATION <=70 THEN 1 ELSE null END AS MIDDLE_CNT,
B.ARRAY_RESOURCE_ID FROM TBL_PM_FRONTEND_PORT_DAILY PFRONTEND_PORT INNER
JOIN TBL_RM_FRONTEND_PORT B ON PFRONTEND_PORT.RESOURCE_ID =
B.INTERNAL_RESOURCE_ID INNER JOIN TBL_RM_STORAGEARRAY C ON
C.INTERNAL_RESOURCE_ID = B.ARRAY_RESOURCE_ID WHERE AGGREGATED_TIME
>=1330841249657 AND AGGREGATED_TIME <=1330927649657 )A GROUP BY
ARRAY_RESOURCE_ID)FRONTEND_PORT_DATA ON C.INTERNAL_RESOURCE_ID
=FRONTEND_PORT_DATA.ARRAY_RESOURCE_ID
SELECT C.NAME, C.SN,C.INTERNAL_RESOURCE_ID
,LUN_DATA.LUN_TOTAL_CNT,LUN_DATA.LUN_LOW_CNT,LUN_DATA.LUN_MIDDLE_CNT
,FRONTEND_PORT_DATA.FRONTEND_PORT_TOTAL_CNT,FRONTEND_PORT_DATA.FRONTEND_PORT_LOW_CNT,
FRONTEND_PORT_DATA.FRONTEND_PORT_MIDDLE_CNT
FROM
TBL_RM_STORAGEARRAY C LEFT JOIN (SELECT COUNT(A.LOW_CNT) AS
LUN_TOTAL_CNT,SUM(A.LOW_CNT) AS LUN_LOW_CNT,SUM(MIDDLE_CNT) AS LUN_MIDDLE_CNT,
ARRAY_RESOURCE_ID FROM (SELECT CASE WHEN PLUN.AVG_UTILIZATION >=0 AND
PLUN.AVG_UTILIZATION <=50 THEN 1 ELSE null END AS LOW_CNT , CASE WHEN
PLUN.AVG_UTILIZATION >50 AND PLUN.AVG_UTILIZATION <=70 THEN 1 ELSE null END AS
MIDDLE_CNT, B.ARRAY_RESOURCE_ID FROM TBL_PM_LUN_DAILY PLUN INNER JOIN
TBL_RM_LUN B ON PLUN.RESOURCE_ID = B.INTERNAL_RESOURCE_ID INNER JOIN
TBL_RM_STORAGEARRAY C ON C.INTERNAL_RESOURCE_ID = B.ARRAY_RESOURCE_ID WHERE
AGGREGATED_TIME >=1330841249657 AND AGGREGATED_TIME <=1330927649657 )A GROUP BY
ARRAY_RESOURCE_ID)LUN_DATA ON C.INTERNAL_RESOURCE_ID
=LUN_DATA.ARRAY_RESOURCE_ID LEFT JOIN (SELECT COUNT(A.LOW_CNT) AS
FRONTEND_PORT_TOTAL_CNT,SUM(A.LOW_CNT) AS FRONTEND_PORT_LOW_CNT,SUM(MIDDLE_CNT)
AS FRONTEND_PORT_MIDDLE_CNT, ARRAY_RESOURCE_ID FROM (SELECT CASE WHEN
PFRONTEND_PORT.AVG_UTILIZATION >=0 AND PFRONTEND_PORT.AVG_UTILIZATION <=50 THEN
1 ELSE null END AS LOW_CNT , CASE WHEN PFRONTEND_PORT.AVG_UTILIZATION >50 AND
PFRONTEND_PORT.AVG_UTILIZATION <=70 THEN 1 ELSE null END AS MIDDLE_CNT,
B.ARRAY_RESOURCE_ID FROM TBL_PM_FRONTEND_PORT_DAILY PFRONTEND_PORT INNER
JOIN TBL_RM_FRONTEND_PORT B ON PFRONTEND_PORT.RESOURCE_ID =
B.INTERNAL_RESOURCE_ID INNER JOIN TBL_RM_STORAGEARRAY C ON
C.INTERNAL_RESOURCE_ID = B.ARRAY_RESOURCE_ID WHERE AGGREGATED_TIME
>=1330841249657 AND AGGREGATED_TIME <=1330927649657 )A GROUP BY
ARRAY_RESOURCE_ID)FRONTEND_PORT_DATA ON C.INTERNAL_RESOURCE_ID
=FRONTEND_PORT_DATA.ARRAY_RESOURCE_ID