Consult everybody a great god a SQL statement statistics, thank you-CodePudding

Database is Oracle, thank you for your great god answered

CodePudding user response:

Select
Case the when a. parking lot='a' then a. car classes end as classes,
Case the when a. parking lot='a' then a. approach end as in total, total
Case the when b. parking lot='a' then b. put end as cars put car number,
Case the when c. parking lot='a' then c. scrapping several end as number,
.
The from
(select car, parking lot, the count (*) as the approach from total play table group by car, parking lot). A,
(select car, parking lot, the sum (cart) as cart number from play table group by car, parking lot) b,
(select car, parking lot, the sum (whether scrap) as scrap number from play table group by car, parking lot) c
Where a. car class=b. car class and a. car class=c. car class and a. parking lot=b. parking lot and a.=c. carparks

Stock cannot be computed, it is necessary to give statistical caliber

CodePudding user response:

reference 1st floor wildwolv response:

select
Case the when a. parking lot='a' then a. car classes end as classes,
Case the when a. parking lot='a' then a. approach end as in total, total
Case the when b. parking lot='a' then b. put end as cars put car number,
Case the when c. parking lot='a' then c. scrapping several end as number,
.
The from
(select car, parking lot, the count (*) as the approach from total play table group by car, parking lot). A,
(select car, parking lot, the sum (cart) as cart number from play table group by car, parking lot) b,
(select car, parking lot, the sum (whether scrap) as scrap number from play table group by car, parking lot) c
Where a. car class=b. car class and a. car class=c. car class and a. parking lot=b. parking lot and a.=c. carparks

Memory cannot be computed, it is necessary to give statistical caliber

select
Case the when a. parking lot='a' then a. car classes end as classes,
Case the when a. parking lot='a' then a. approach end as in total, total
Case the when b. parking lot='a' then b. put end as cars put car number,
Case the when c. parking lot='a' then c. scrapping several end as number,
.
The from
(select car, parking lot, the count (*) as the approach from total play table group by car, parking lot order by car class). A,
(select car, parking lot, the sum (cart) as cart number from play table group by car, parking lot order by car class) b,
(select car, parking lot, the sum (whether scrap) as scrap number from play table group by car, parking lot order by car class) c
Where a. Car class=b. car class and a. car class=c. class

Stock cannot be computed, it is necessary to give statistical caliber

CodePudding user response:

 with tab1 as (
Select 1 car "class", 'a' typ, 1 c1, c2, c3, c4 from 4 dual union all 
Car select 3 "class", 'a' typ, 12 c1 and c2 and c3, c4 from 45 dual union all 
Select 3 id, 'b' typ, 11 c1 and c2, c3, c4 from 4 dual 
) 
Select * from tab1 t1 
The pivot (Max (c1) c1, Max (c2) c2, Max (c3) c3, Max (c4) for c4 for typ in (' a 'typ_a,' b 'typ_b)) 
;

You can also use the traditional longitudinal transverse writing, but a bit too much of a traditional upstairs,,,

CodePudding user response:

The

reference 3 floor nayi_224 response:

 with tab1 as (
Select 1 car "class", 'a' typ, 1 c1, c2, c3, c4 from 4 dual union all 
Car select 3 "class", 'a' typ, 12 c1 and c2 and c3, c4 from 45 dual union all 
Select 3 id, 'b' typ, 11 c1 and c2, c3, c4 from 4 dual 
) 
Select * from tab1 t1 
The pivot (Max (c1) c1, Max (c2) c2, Max (c3) c3, Max (c4) for c4 for typ in (' a 'typ_a,' b 'typ_b)) 
;

Can also use the traditional longitudinal and transverse writing, but a bit too much of a traditional upstairs,,,

I wrote all basic database can be universal

CodePudding user response:

SELECT the vehicle type,
t.A parking lot in total,
Put A car parking lot number,
A number of scrap car park,
A parking lot stock,
Total number of parking lot B comes into play,
Parking lot B put car number,
Parking lot B scrap number,
The parking lot B stock
The FROM (
SELECT the vehicle type,
t.The SUM (CASE WHEN parking lot='a' t. THEN 1 ELSE 0 END) a parking lot in total,
The SUM (CASE WHEN parking lot='a' AND t. t. whether cart=1 THEN 1 ELSE 0 END) put a car parking lot number,
The SUM (CASE WHEN parking lot='a' AND t. t. whether scrap=1 THEN 1 ELSE 0 END) parking lot a scrap number,
The SUM (CASE WHEN T='a' parking lot THEN 1 ELSE 0 END) - the SUM (CASE WHEN parking lot='a' AND t. t. whether cart=1 THEN 1 ELSE 0 END) parking lot a stock,
The SUM (CASE WHEN parking lot='b' t. THEN 1 ELSE 0 END) total number of parking lot b comes into play,
The SUM (CASE WHEN parking lot='b' AND t. t. whether cart=1 THEN 1 ELSE 0 END) put a car parking lot b,
The SUM (CASE WHEN parking lot='b' AND t. t. whether scrap=1 THEN 1 ELSE 0 END) parking lot b scrap number,
The SUM (CASE WHEN T='b' parking lot THEN 1 ELSE 0 END) - the SUM (CASE WHEN parking lot='b' AND t. t. whether cart=1 THEN 1 ELSE 0 END) parking lot b stock,
TY 1
The FROM play table T GROUP BY t. vehicle type
UNION ALL
SELECT the 'total' vehicle type,
The SUM (CASE WHEN parking lot='a' t. THEN 1 ELSE 0 END) a parking lot in total,
The SUM (CASE WHEN parking lot='a' AND t. t. whether cart=1 THEN 1 ELSE 0 END) put a car parking lot number,
The SUM (CASE WHEN parking lot='a' AND t. t. whether scrap=1 THEN 1 ELSE 0 END) parking lot a scrap number,
The SUM (CASE WHEN T='a' parking lot THEN 1 ELSE 0 END) - the SUM (CASE WHEN parking lot='a' AND t. t. whether cart=1 THEN 1 ELSE 0 END) parking lot a stock,
The SUM (CASE WHEN parking lot='b' t. THEN 1 ELSE 0 END) total number of parking lot b comes into play,
The SUM (CASE WHEN parking lot='b' AND t. t. whether cart=1 THEN 1 ELSE 0 END) put a car parking lot b,
The SUM (CASE WHEN parking lot='b' AND t. t. whether scrap=1 THEN 1 ELSE 0 END) parking lot b scrap number,
The SUM (CASE WHEN T='b' parking lot THEN 1 ELSE 0 END) - the SUM (CASE WHEN parking lot='b' AND t. t. whether cart=1 THEN 1 ELSE 0 END) parking lot b stock,
2 TY
The FROM play table T GROUP BY t. vehicle type) T ORDER BY T.T Y, t. vehicle types;

CodePudding user response:

reference 4 floor wildwolv response:

Quote: reference nayi_224 reply: 3/f

 with tab1 as (
Select 1 car "class", 'a' typ, 1 c1, c2, c3, c4 from 4 dual union all 
Car select 3 "class", 'a' typ, 12 c1 and c2 and c3, c4 from 45 dual union all 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull