ID user name meter table number table number meter word wheel meter reading date value for the unit price
Zhang SAN 1 14060001 10 2014-6-1
2.5Zhang SAN 1 14060001 14 2014-6-9
2.5Zhang SAN 1 14060002 2014-6-9 2.5
(note: this RiBiao number for 14060001 is out of order, continue to use the number in the table of 14060002 table)
Zhang SAN 1 14060002 10 2014-6-30
2.5Zhang SAN 2, 14060003 1 2014-6-1
2.51 zhang SAN 2 14060003 10 2014-6-30 2.5
Li si 1 14060004 2 2014-6-1 2.5
2 li si 1 14060004 18 2014-6-30
2.5
At the end of the month, according to the above data generated settlement report, the following format
ID: 1 user name: zhang
Water meter number serial number table table at least check meter date code water meter reading date unit price cost
1, 14060001 to 10 2014-6-1 14 2014-6-1 4 2.5 10
1 14060002 3 2014-6-9 10 2014-6-9 7 2.5 17.5
2 14060003 1 2014-6-1 10 2014-6-1 9 2.5 22.5
Total cost: 50
ID: 2 user name: li si
Water meter number serial number table table at least check meter date code water meter reading date unit price cost
1 14060004 2 2014-6-1 18 2014-6-1 16 2.5 40
Total cost: 40
Excuse me, this report which report control can be achieved?
CodePudding user response:
Format a bit of a mess, I'll clean up:
Existing data as follows: zhang home has two positive measurement meter, Li Sijia only a positive measurement meter
ID user name meter table number table number meter word wheel meter reading date value for the unit price
Zhang SAN 1 14060001 10 2014-6-1
2.5Zhang SAN 1 14060001 14 2014-6-9
2.5Zhang SAN 1 14060002 2014-6-9 2.5
(note: this RiBiao number for 14060001 table is broken, change the table number for 14060002 continue to use the table)
Zhang SAN 1 14060002 10 2014-6-30
2.5Zhang SAN 2, 14060003 1 2014-6-1
2.51 zhang SAN 2 14060003 10 2014-6-30 2.5
Li si 1 14060004 2 2014-6-1 2.5
2 li si 1 14060004 18 2014-6-30
2.5
At the end of the month, according to the above data generated settlement report, the following format
ID: 1 user name: zhang
Water meter number serial number table table at least check meter date code water meter reading date unit price cost
1, 14060001 to 10 2014-6-1 14 2014-6-1 4 2.5 10
1 14060002 3 2014-6-9 10 2014-6-9 7 2.5 17.5
2 14060003 1 2014-6-1 10 2014-6-1 9 2.5 22.5
Total cost: 50
ID: 2 user name: li si
Water meter number serial number table table at least check meter date code water meter reading date unit price cost
1 14060004 2 2014-6-1 18 2014-6-1 16 2.5 40
Total cost: 40
Excuse me, this report which report control can be achieved?
CodePudding user response:
Or not neat, I uploaded a picture
CodePudding user response:
The first is to query the data- this is the grammar of the SQL Server
The SELECT T0. ID,
T0. The user name,
T0. Water meter table number,
T0 in table,
T1. Water meter word wheel in value at the very least,
Date of T0. At least,
T2. Water meter word wheel in value check code,
T0. Check date code,
(T2) meter word wheel - T1. Value for water meter word wheel) value for water use,
T2. The unit price,
(T2) water meter word wheel - value for T1) water meter word wheel) value for * T2. The unit price cost
The FROM (SELECT ID,
The user name,
Water meter table number,
Table,
Date of MIN (meter) at least date,
Date of MAX (meter) check code date
The FROM list
WHERE meter reading date BETWEEN '2014-06-01' AND '2014-06-30 s'
) T0
The JOIN list T1
ON T1. ID=T0. ID
AND T1. Water meter table number=T0. Water meter table number
AND T1. Table number=T0. Table no.
AND T1. The meter reading date=T0. At least the date
Schedule of the JOIN T2
ON T2. ID=T0. ID
AND T2. Water meter table number=T0. Water meter table number
AND T2. Table number=T0. Table no.
AND T2. The meter reading date=T0. Check code date
And then grouping combined control has the function of general statements, which can be,
CodePudding user response:
If I deal with this kind of problem, is oneself use code to "summary",Why always thinking about "do things to others"?
Let the "third party" for you, can't be anything can "contentment",