View the database-CodePudding

For the great god, how do you build this total table view?
View there is no date, but I hope with the conditions of the start date and end date, behind on the web page input, you can get corresponding date within the total amount of new signing

CodePudding user response:

 
Declare @ tb1 table (region nvarchar (10), the amount of money, time datetime, contract number varchar (10)) 
Declare @ tb2 table (region nvarchar (10), the amount of money, time datetime, contract number varchar (10)) 

Select sum (a. amount) as the total contract amount, the sum (b. amount) as total amount to the account 
The from @ tb1 a 
Left the join @ tb2 on a. b contract number=b. contract number 
Where a. Time between '2019-1-1' and '2020-1-1 s'

CodePudding user response:

reference 1/f, illiterate old gu response:

 
Declare @ tb1 table (region nvarchar (10), the amount of money, time datetime, contract number varchar (10)) 
Declare @ tb2 table (region nvarchar (10), the amount of money, time datetime, contract number varchar (10)) 

Select sum (a. amount) as the total contract amount, the sum (b. amount) as total amount to the account 
The from @ tb1 a 
Left the join @ tb2 on a. b contract number=b. contract number 
Where a. Time between '2019-1-1' and '2020-1-1 s'

I could express is not very clear, and then send a figure

CodePudding user response:

Don't preach photos, describe the problem in words clearly

The correct description of the problem, to various aspects of communication, such as: original) (completely don't understand the technology, the product manager (thinking of wonderful work), the project manager (rigid) personality, colleagues (not a channel)

CodePudding user response:

New signing table:
Field area, new signing amount, new signing time, contract number;
Values such as (" Beijing ", 1000201, 9-1-1 '001'),
(hefei, 500, 2019-5-1, '002'),
(Beijing, 2000201-8-1, '003')
To watch:
Field region, to the amount, to the time, the contract;
Values such as (" Beijing ", 500, 2019-4-1, '001'),
(" tianjin ", 500, 2019-3-1, '004'),
(tianjin, 2000201 9-10-1, '005')
Need to create a view:
Hefei area Beijing tianjin Shanghai...
New signing + 2000 500 1000 0 0...
To 500 0 500 + 2000 0...

Query conditions to join the start time and end time, such as input for the 2019-1-1 start time, end time input for the 2019-5-1, then in the view of new signing and to new signing, for the time to watch and the amount of the corresponding region in the
Another: region's collection is fixed, such as a total of 10 areas, but new signing table and doesn't cover all areas to watch in the region, if not in the table view in the region, so the region corresponding to the new signing and to amount to zero,

CodePudding user response:

Total table and finally a list of aggregate in a row

CodePudding user response:

You this,,, use stored procedures, with a view, not too good, transfer line column outside the group, but the conditions should be and group, at the same conditions should also be good at turning inside, so,,

CodePudding user response:

 declare @ tb1 table (region nvarchar (10), the amount of money, time, date, contract number varchar (10)) 
Declare @ tb2 table (region nvarchar (10), the amount of money, time, date, contract number varchar (10)) 
Insert into @ tb1 values (" Beijing ", 1000, '2019-1-1', '001'), (' hefei, 500, '2019-5-1', '002'), (" Beijing ", 2000, '2019-8-1', '003') 
Insert into @ tb2 values (" Beijing ", 500, '2019-4-1', '001'), (" tianjin ", 500, '2019-3-1', '004'), (" tianjin ", 2000, '2019-10-1', '005') 


The select area, the sum (Beijing), sum (tianjin), sum (Shanghai), sum (hefei), sum (chongqing), sum (shenzhen), sum (guangzhou) 
The from (
Select 'new signing as places, time, Beijing, tianjin, Shanghai, chongqing, hefei, shenzhen, guangzhou 
The from @ tb1 
Pivot (Max (amount) for region (in hefei, Shanghai, Beijing, tianjin, chongqing, shenzhen, guangzhou)) p 
Union all 
Select the 'to' the as places, time, Beijing, tianjin, Shanghai, chongqing, hefei, shenzhen, guangzhou 
The from @ tb2 
Pivot (Max (amount) for region (in hefei, Shanghai, Beijing, tianjin, chongqing, shenzhen, guangzhou)) p 
) a 
- where the time between '2019-1-1' and '2019-5-1 s' 
Group by region

Probably wondering, transfer line column in advance, and then group, take a look at this way can get into view

CodePudding user response:

SELECT 'new signing AS the TYPE, AREA AS AREA, new signing AMOUNT AS the AMOUNT of new signing time AS OPR_DATE, CONTRACT number AS CONTRACT FROM new signing table
UNION ALL
SELECT 'to' areas, and to the amount of T, to the time, contract number FROM to watch

With more than a view, and then to view OPR_DATE for queries on time, the results set in CASE of any further the WHEN or the PIVOT to try to convert the ranks,

CodePudding user response:

refer to 7th floor illiterate old gu response:

 declare @ tb1 table (region nvarchar (10), the amount of money, time, date, contract number varchar (10)) 
Declare @ tb2 table (region nvarchar (10), the amount of money, time, date, contract number varchar (10)) 
Insert into @ tb1 values (" Beijing ", 1000, '2019-1-1', '001'), (' hefei, 500, '2019-5-1', '002'), (" Beijing ", 2000, '2019-8-1', '003') 
Insert into @ tb2 values (" Beijing ", 500, '2019-4-1', '001'), (" tianjin ", 500, '2019-3-1', '004'), (" tianjin ", 2000, '2019-10-1', '005') 


The select area, the sum (Beijing), sum (tianjin), sum (Shanghai), sum (hefei), sum (chongqing), sum (shenzhen), sum (guangzhou) 
The from (
Select 'new signing as places, time, Beijing, tianjin, Shanghai, chongqing, hefei, shenzhen, guangzhou 
The from @ tb1 
Pivot (Max (amount) for region (in hefei, Shanghai, Beijing, tianjin, chongqing, shenzhen, guangzhou)) p 
Union all 
Select the 'to' the as places, time, Beijing, tianjin, Shanghai, chongqing, hefei, shenzhen, guangzhou 
The from @ tb2 
Pivot (Max (amount) for region (in hefei, Shanghai, Beijing, tianjin, chongqing, shenzhen, guangzhou)) p 
) a 
- where the time between '2019-1-1' and '2019-5-1 s' 
Group by region

Probably wondering, transfer line column in advance, and then group, take a look at this way into view can use

stored procedure is how to do it? Directly in the code written in computing?