Regional data for a time period and, where duplicate regions are found not added

There are 2 of tables
The first table is f_city (field: city_id and field City_name)
The second table is F_chengjiao (field: ID, city_id, area, Taoshu, Fang_time)
The associated field is city_id

The statement I wrote was

SELECT CONCAT (fc.city_name), sum (Fcj.area), sum (Fcj.taoshu) from F_chengjiao as FCJ left JOIN F_city as FC USING (CITY_ID) WHERE ". $sql." GROUP by Fcj.fang_time ORDER by fc.city_id

The result is that the duplicated regions are not merged and added

Reply to discussion (solution)

Grouping is GROUP by fcj.fang_time
It's natural that there will be duplicate regions without merging and adding
Because the same fc.city_name may have different fcj.fang_time values (obviously a time)

Grouping is GROUP by fcj.fang_time
It's natural that there will be duplicate regions without merging and adding
Because the same fc.city_name may have different fcj.fang_time values (obviously a time)

Thank you hint, but now there is a problem is that in this statistical time, some areas of data because there is no data to add data how to let no data in the SQL Add region display 0?

F_chengjiao as FCJ left JOIN F_city as FC
Switch
F_chengjiao as FCJ right JOIN F_city as FC
Or
F_city as FC left JOIN F_chengjiao as FCJ

Thank you hint, but now there is a problem is that in this statistical time, some areas of data because there is no data to add data how to let no data in the SQL Add region display 0?

Then you have to tell the database the list of all your regions! is to take the result set you now get, depending on the region of this field, Associate (left/right join) List of all your regions

F_chengjiao as FCJ left JOIN F_city as FC
Switch
F_chengjiao as FCJ right JOIN F_city as FC
Or
F_city as FC left JOIN F_chengjiao as FCJ

It is still not possible to show the 0 effect of the area without adding data, as did the previous one.

Thank you hint, but now there is a problem is that in this statistical time, some areas of data because there is no data to add data how to let no data in the SQL Add region display 0?

Then you have to tell the database the list of all your regions! is to take the result set you now get, depending on the region of this field, Associate (left/right join) List of all your regions

All regions are present the first table is f_city (field: city_id and field City_name)

You want to group by City_name.

You want to group by City_name.

It's press City_name.

SELECT CONCAT (fc.city_name), sum (Fcj.area), sum (Fcj.taoshu) from F_chengjiao as FCJ right JOIN f_city as FC USING (city_id ) WHERE ". $sql." GROUP by Fc.city_name ORDER by fc.city_id

SELECT * from Region table
Left JOIN tab on area table. City_name = statistics. City_name
GROUP by area table. city_name

This using (city_id) thing I do not quite understand and on what the difference, so did not change on your SQL!

SELECT * from Region table
Left JOIN tab on area table. City_name = statistics. City_name
GROUP by area table. city_name

This using (city_id) thing I do not quite understand and on what the difference, so did not change on your SQL!

USING (city_id) is a shorthand for table association fields

SELECT * from Region table
Left JOIN tab on area table. City_name = statistics. City_name
GROUP by area table. city_name

This using (city_id) thing I do not quite understand and on what the difference, so did not change on your SQL!

City_name is not an association field, so the search does not

City_name is not an association field, so the search does not

How does the data in your data table know which city he belongs to, with an ID or a name? Just use that field to make the associated condition!

City_name is not an association field, so the search does not

How does the data in your data table know which city he belongs to, with an ID or a name? Just use that field to make the associated condition!

Use, or can not show that no data added to the city of 0 effect, only the results of the added data will be displayed

I understand that's the way it is! Is that the effect you want?

I understand that's the way it is! Is that the effect you want?

Sorry back to the information late, login account opened the capital has been logged on, the account is locked.

Do you still use conditional statements in this? Don't understand the meaning of this conditional sentence, can you explain it?
I tried to use your method of writing modified, because the area to sum, with the conditional statement after the error, there is a problem is that the table associated with your search city name how not to bring the table prefix

1. Do you still use conditional statements in this?
1) If your "conditional statement" refers to where, add it after the left join
2) If your "conditional statement" refers to the relationship condition on the back of on, look at my 12 floor reply, the goal is to let the data table know each city from the city table of data.
2. Regional summation
SELECT city_name,if (Toushu is Null,0,sum (Toushu))
From t_city
Left JOIN T_chengjiao on t_city.cid= t_chengjiao.cid
GROUP by T_chengjiao.cid
3. When you search for a city name after the table is associated, why don't you take the table prefix?
After a table is associated with a table, there are no fields with duplicate names, which can be prefixed.

1. Do you still use conditional statements in this?
1) If your "conditional statement" refers to where, add it after the left join
2) If your "conditional statement" refers to the relationship condition on the back of on, look at my 12 floor reply, the goal is to let the data table know each city from the city table of data.
2. Regional summation
SELECT city_name,if (Toushu is Null,0,sum (Toushu))
From t_city
Left JOIN T_chengjiao on t_city.cid= t_chengjiao.cid
GROUP by T_chengjiao.cid
3. When you search for a city name after the table is associated, why don't you take the table prefix?
After a table is associated with a table, there are no fields with duplicate names, which can be prefixed.

I said the conditional sentence is if (Toushu is Null,0,sum (Toushu)), do not understand.

I changed it again, still can't come out

SELECT CONCAT (Fc.city_name), if (Fcj.area is Null,0,sum (Fcj.area)), if (Fcj.taoshu are Null,0,sum (Fcj.taoshu)) from f_city As FC left JOIN F_chengjiao as FCJ USING (city_id) WHERE ". $sql." GROUP by fc.city_id ORDER by fc.city_id

As a result (i reserved a city_id=13 without adding data, but not shown)

You export the data, I'll test it.
You're not going to let me make a data sheet out of your head.

You export the data, I'll test it.
You're not going to let me make a data sheet out of your head.

--phpMyAdmin SQL dump--version 3.5.1--http://www.phpmyadmin.net----Host: localhost--generated date: September 13, 2014 02:48--Server Edition Ben: 5.5.24-log--PHP version: 5.3.13SET sql_mode= "No_auto_value_on_zero"; Set time_zone = "+00:00";/*!40101 set @OLD_CHARACTER_SET_CLIENT =@ @CHARACTER_SET_CLIENT */;/*!40101 set @OLD_CHARACTER_ set_results=@ @CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION =@ @COLLATION_CONNECTION */;/*!40101 SET NAMES UTF8 */;----database: ' FANG01 '----------------------------------------------------------------table structure ' f_ Chengjiao '--create TABLE IF not EXISTS ' F_chengjiao ' (' ID ' mediumint (8) unsigned not NULL auto_increment, ' city_id ' Med Iumint (8) Not NULL, ' Iszhu ' char (1) is not null DEFAULT ' 1 ' COMMENT '//residential or non-residential, 1 is residential ', ' area ' int (ten) NOT null COMMENT '//Size ', ' Taoshu ' int (ten) NOT null COMMENT '//units ', ' fang_time ' date not null COMMENT '///Real Estate Information Network storage time ', ' Os_time ' datetime not N ULL COMMENT '//Storage computer time ', PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 auto_increment=14;---- Dump the data in the table ' F_chengjiao '--insert into ' f_chengjiao ' (' IDs ', ' city_id ', ' Iszhu ', ' area ', ' Taoshu ', ' fang_time ', ' os_time ') VAL UES (1, 1, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (2, 2, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), ( 3, 3, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (4, 4, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (5, 5 , ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (6, 6, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (7, 7, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (8, 8, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (9, 9, ' 1 ', 1 00, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (10, 10, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (11, 11, ' 1 ', 1 00, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (12, 12, ' 1 ', 100, 200, ' 2014-09-01 ', ' 2014-09-01 00:00:00 '), (13, 1, ' 1 ', 10 0, $, ' 2014-09-08 ', ' 2014-09-01 00:00:00 '); The structure of the--------------------------------------------------------------table ' f_ City '--create TABLE IF not EXIsts ' f_city ' (' city_id ' mediumint (8) Not NULL, ' city_name ' varchar (TEN) NOT NULL, PRIMARY KEY (' city_id ')) Engine=inno DB DEFAULT Charset=utf8;----Dump the data in the table ' f_city '--insert into ' f_city ' (' city_id ', ' City_name ') VALUES (1, ' Nanchang '), (2, ' East L. District '), (3, ' West L. District '), (4, ' Castle Peak Lake area '), (5, ' Qingyunpu District '), (6, ' Wanli District '), (7, ' open area '), (8, ' High tech Zone '), (9, ' Red Valley Beach Area '), (10, ' Sang Hai '), (11, ' Heroes '), (12, ' Nanchang County '), (13, ' New county ');/*!40101 set character_set_client= @OLD_CHARACTER_SET_CLIENT */;/*!40101 set character_set_results= @OLD_ Character_set_results */;/*!40101 SET collation_connection= @OLD_COLLATION_CONNECTION */;

Check your where statement

Check your where statement

Where is the choice of a time period

$sql = ", if ($start _time && $end _time) {    $sql =" Fcj.fang_time >= ' ". $start _time." ' and fcj.fang_time<= ' ". $end _time." ";} else if ($start _time) {        $sql = "Fcj.fang_time = '". $start _time. " ";} else if ($end _time) {    $sql = "Fcj.fang_time = '". $end _time. " ";} else{    $sql = "fcj.city_id=". $city. "' ";}

SELECT city_name, sum (area) as ' size ', sum (Taoshu) as ' arrays ' from f_city left join F_chengjiao on F_city.city_id=f_chengjiao.ci TY_ID GROUP BY f_city.city_id

The right link is the same.

SELECT city_name, sum (area) as ' size ', sum (Taoshu) as ' arrays ' from f_city left join F_chengjiao on F_city.city_id=f_chengjiao.ci TY_ID GROUP BY f_city.city_id

Strange, I wrote on the 8 floor of the statement and your similar, how can not come out, you can tell me is there wrong?

SELECT CONCAT (fc.city_name), sum (Fcj.area), sum (Fcj.taoshu) from F_chengjiao as FCJ right JOIN f_city as FC USING (city_id ) WHERE ". $sql." GROUP by fc.city_id ORDER by fc.city_id

You get rid of your where statement first

You get rid of your where statement first

That's where it's going to be, but I'm going to get rid of it and I can't choose to query the results over a period of time.

Not that you can't add where is your where there is a problem in PHP echo your SQL statement, put it in MySQL to check ~ to see where there is a problem!

What is so strange about that?
The F_chengjiao table does not have the data of the new county, if filtered by the time period, will inevitably filter him out
If you want to keep all the city_name, you need to write this.

Select City_name, sum (area) as ' size ', sum (Taoshu) as ' arrays ' from f_city LEFT join (SELECT * from F_chengjiao where condition) T on F_ CITY.CITY_ID=T.CITY_ID GROUP BY f_city.city_id

What is so strange about that?
The F_chengjiao table does not have the data of the new county, if filtered by the time period, will inevitably filter him out
If you want to keep all the city_name, you need to write this.

Select City_name, sum (area) as ' size ', sum (Taoshu) as ' arrays ' from f_city LEFT join (SELECT * from F_chengjiao where condition) T on F_ CITY.CITY_ID=T.CITY_ID GROUP BY f_city.city_id

I searched the database, and did not filter out, you see

What kind of conditional expression are you?

Not that you can't add where is your where there is a problem in PHP echo your SQL statement, put it in MySQL to check ~ to see where there is a problem!

A time period has been printed:

What kind of conditional expression are you?

Isn't that what it says? This is the only way to get out, or you can see the 31 floor, the print condition is

What is so strange about that?
The F_chengjiao table does not have the data of the new county, if filtered by the time period, will inevitably filter him out
If you want to keep all the city_name, you need to write this.

Select City_name, sum (area) as ' size ', sum (Taoshu) as ' arrays ' from f_city LEFT join (SELECT * from F_chengjiao where condition) T on F_ CITY.CITY_ID=T.CITY_ID GROUP BY f_city.city_id

Here is how to have a T, why use, use your writing all come out, did not understand you so write (select * from F_chengjiao where condition) meaning, here Search all the fields and directly with F_chengjiao difference not understand

You don't understand.
SELECT * FROM F_chengjiao where condition
The meaning of???

1. Why is there a t here?
T is only an alias of the associated temporary table, no special meaning, you get rid of, he will error.
2. Differences
1) First, the association after the search, so that the record (Xinjian County) does not meet the conditions will not be displayed. (To understand this sentence is very simple, you select * out to see, some records are not satisfied with your where condition)
2) Two, first search (F_CHENGJIAO) after the association, so that it will not affect the final number of results.

You don't understand.
SELECT * FROM F_chengjiao where condition
The meaning of???

Meaning I know, but where is the place to put in the F_chengjiao first, instead of putting it outside

1. Why is there a t here?
T is only an alias of the associated temporary table, no special meaning, you get rid of, he will error.
2. Differences
1) First, the association after the search, so that the record (Xinjian County) does not meet the conditions will not be displayed. (To understand this sentence is very simple, you select * out to see, some records are not satisfied with your where condition)
2) Two, first search (F_CHENGJIAO) after the association, so that it will not affect the final number of results.

Oh, I understand, but do aliases use as?

All as can be omitted.

All as can be omitted.

The original is this, I read in the book is required to write as, at the same time I also want to ask a question, is according to your request to write, f_chengjiao table are searched 2 times, efficiency will be low point, later this piece of data more, how to abbreviated under?

1, this is according to your request, and not my request
2, f_chengjiao table only searched 1 times, how is 2 times?

1, this is according to your request, and not my request
2, f_chengjiao table only searched 1 times, how is 2 times?

The City_name, area, and Taoshu of the first search are in table F_chengjiao
The second time I searched for select * from F_chengjiao, wasn't it 2 times?

