Home Php C# Sql C C++ Javascript Python Java Go Android Git Linux Asp.net Django .net Node.js Ios Xcode Cocoa Iphone Mysql Tomcat Mongodb Bash Objective-c Scala Visual-studio Apache Elasticsearch Jar Eclipse Jquery Ruby-on-rails Ruby Rubygems Android-studio Spring Lua Sqlite Emacs Ubuntu Perl Docker Swift Amazon-web-services Svn Html Ajax Xml Java-ee Maven Intellij-idea Rvm Macos Unix Css Ipad Postgresql Css3 Json Windows-server Vue.js Typescript Oracle Hibernate Internet-explorer Github Tensorflow Laravel Symfony Redis Html5 Google-app-engine Nginx Firefox Sqlalchemy Lucene Erlang Flask Vim Solr Webview Facebook Zend-framework Virtualenv Nosql Ide Twitter Safari Flutter Bundle Phonegap Centos Sphinx Actionscript Tornado Register | Login | Edit Tags | New Questions | 繁体 | 简体


10 questions online user: 39

0
votes
answers
21 views
+10

Postgres加入不正確的更新

0

我想在Postgres上運行一些更新查詢,這些查詢是在公共字段上加入表的。這是用SQL Server運行的,更新計數是1,這是預期的,而postgres的更新計數是3.似乎postgres不執行隱式連接,當目標表與連接中涉及的源表名稱相同時。該腳本可以更DESCRIPT比在說什麼,那就是:Postgres加入不正確的更新

drop table test; 
drop table test2; 
create table test(col1 int, col2 varchar(10)); 
insert into test values(1, 'aaa'); 
insert into test values(2, 'bbb'); 
insert into test values(3, 'ccc'); 
create table test2(col1 int); 
insert into test2 values(2); 
select * from test; 
select * from test2; 

// Select join = rowcount 1 
select count(*) from test t2, test2 t3 
where t2.col1 = t3.col1; 

// SQL Server update = 1; postgres =3 
update test set col2 = 'changed' 
from test t2, test2 t3 
where t2.col1 = t3.col1; 

上面的查詢可以簡化爲:

update test set col2 = 'changed' 
from test2 where test.col1 = test2.col1; 

但是這不是我的本意爲加盟條款可能涉及一些更多的聯合聲明。 期望意圖是運行這樣的查詢:

UPDATE IDMAP_CHILD_JOBID SET RESTORESUCCESS = IDMAP_TABLE_JOBID.RESTORESUCCESS, RESTOREERRMSG = IDMAP_TABLE_JOBID.RESTOREERRMSG 
FROM CHILD, IDMAP_TABLE_JOBID 
WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID 
AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $FALSE 

Postgres的與表抱怨指定多次如果IDMAP_CHILD_JOBID相同IDMAP_TABLE_JOBID更多。這怎麼可以重寫? 我的應用程序應該生成一個更新語句,這是統一的查詢應該運行,而行爲是不同的。顯然,加入是在選擇時執行的,而不是正在更新。

+1

[從手動報價](https://www.postgresql.org/docs/current/static/sql-update.html):「*注意,目標表必須** **未出現在from_list ,除非你打算自我加入*「。你需要從'FROM'部分 –

+0

刪除''IDMAP_TABLE_JOBID'一些聯接statements'有沒有這樣的事,作爲一個* JOIN語句*。 JOIN是一個二元**運算符**,它的操作數是兩個表(表達式),其結果是一個表表達式。 – wildplasser

+0

@wildplasser我離題了。 SQL Server更新可以運行JOIN。可能不會使用術語,但可以通過SELECT with JOINS獲得相似性。 – dmachop

沙发
0
1

如果您需要引用同一個表兩次(或更多),你需要給他們不同的別名,讓您可以參考他們每個人:


update test t1 
set col2 = 'changed' 
from test t2 --  <<-- same table !! 
JOIN test2 t3 ON t2.col1 = t3.col1; 
where t1.something =t3.something  --<<-- link the target to the source 
     ; 

UPDATE idmap_child_jobid dst -- <<-- alias=dst 
SET restoresuccess = src.restoresuccess 
, restoreerrmsg = i.restoreerrmsg 
FROM idmap_table_jobid src -- <<-- same table, different alias=src 
JOIN child c ON c.fk1 = src.oldid 
WHERE c.id = dst.oldid 
AND dst.restoresuccess = false 
     ; 

[未經檢驗的,因爲我不知道該表的定義,或者INTEN查詢系統蒸發散]


This is run with SQL Server

SQL服務器有一個稍微不同的語法(和語義)更新,在:

UPDATE test 
SET field= 'OMG' 
FROM test t1 
JOIN othertable t2 ON t1.field = t2.field 
    ; 

...有只有兩張表在範圍表中;即使test被提到了兩次,這是相同的實體)這是微軟/的Sybase 功能

+0

反芻剛纔你所說的一切: 的Postgres 更新測試d 集COL2 =從測試T1「改爲」 ,test2的T 2,其中t1.col1 = t2.col1和d .something = t2.something; SQL服務器 更新d 組d.col2 = '改爲' 從測試d,測試T1,T2 TEST2其中t1.col1 = t2.col1和d.something = t2.something; – dmachop

+0

我正在寫不得不產生這樣的SQL語句的方法和這一標準少UPDATE語句加入似乎是一場噩夢 !例如: 字符串updateOnJoin(字符串destTable,集<一對<字符串,字符串>> setPairs,收藏個fromTables,收集<配對<字符串,字符串>> joinStatements) 每呼叫者不得不產生別名和構建這似乎是一個難於編碼方式的方法:( – dmachop

板凳
0
0

對於這個SQL查詢:

update test 
    set col2 = 'changed' 
    from test t2, test2 t3 
    where t2.col1 = t3.col1; 

等效Postgres的語法是:

update test 
    set col2 = 'changed' 
    from test2 t3 
    where test.col1 = t3.col1; 

update/join語法是在兩個數據庫不同(雖然我覺得上面還將努力在SQL Server)。

+0

這不是我的意圖。這是我想要做的簡化。在我的問題UPDATE IDMAP_CHILD_JOBID SET RESTORESUCCESS = IDMAP_TABLE_JOBID.RESTORESUCCESS,RESTOREERRMSG = IDMAP_TABLE_JOBID.RESTOREERRMSG 從子,IDMAP_TABLE_JOBID WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $ FALSE最終查詢是我的本意,但似乎沒有要在SQL服務器和Postgres使用方式時IDMAP_TABLE_JOBID相同IDMAP_CHILD_JOBID。這個度假勝地別名具有兩種不同的語法:( – dmachop

地板
0
0

我有點困惑,似乎有 3 tables: IDMAP_CHILD_JOBID, CHILD, IDMAP_TABLE_JOBID

簡體查詢(SQry) 2 tables: test,test2和 最終查詢(FQry)之間的差異要更新SQry單列:(注意,T1別名希望防止table specified more than once錯誤)

update test t1 
    set col2 = 'changed' 
    from test t2, test2 t3 
where t2.col1 = t3.col1 
    and t1.col1 = t3.col1; 

所以FQry將更新行從該查詢返回的,如果有在IDMAP_CHILD_JOBID多行匹配CHILD.ID其中,他們都將得到更新。

select IDMAP_CHILD_JOBID.RESTORESUCCESS, 
     IDMAP_TABLE_JOBID.RESTORESUCCESS, RESTOREERRMSG, 
     IDMAP_TABLE_JOBID.RESTOREERRMSG 
FROM IDMAP_CHILD_JOBID, CHILD, IDMAP_TABLE_JOBID 
WHERE CHILD.ID = IDMAP_CHILD_JOBID.OLDID 
    AND CHILD.FK1 = IDMAP_TABLE_JOBID.OLDID 
    AND IDMAP_TABLE_JOBID.RESTORESUCCESS = $FALSE 
14
votes
answers
34 views
+10

SQL join: where clause vs. on clause

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different.


What is the difference and what should go in each?

If I understand the theory correctly, the query optimizer should be able to use both interchangeably.

沙发
+80

In terms of the optimizer, it shouldn't make a difference whether you define your join clauses with ON or WHERE.

However, IMHO, I think it's much clearer to use the ON clause when performing joins. That way you have a specific section of you query that dictates how the join is handled versus intermixed with the rest of the WHERE clauses.

板凳
+10

I think it's the join sequence effect. In the upper left join case, SQL do Left join first and then do where filter. In the downer case, find Orders.ID=12345 first, and then do join.

地板
+10

For an inner join, WHERE and ON can be used interchangeably. In fact, it's possible to use ON in a correlated subquery. For example:

update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)

This is (IMHO) utterly confusing to a human, and it's very easy to forget to link table1 to anything (because the "driver" table doesn't have an "on" clause), but it's legal.

4楼
+10

for better performance tables should have a special indexed column to use for JOINS .

so if the column you condition on is not one of those indexed columns then i suspect it is better to keep it in WHERE .

so you JOIN using the indexed columns, then after JOIN you run the condition on the none indexed column .

5楼
+10

Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It’s possible, though that you might want to filter one or both of the tables before joining them. i.e, the where clause applies to the whole result set whereas the on clause only applies to the join in question.

6楼
+10

In SQL, the 'WHERE' and 'ON' clause,are kind of Conditional Statemants, but the major difference between them are, the 'Where' Clause is used in Select/Update Statements for specifying the Conditions, whereas the 'ON' Clause is used in Joins, where it verifies or checks if the Records are Matched in the target and source tables, before the Tables are Joined

For Example: - 'WHERE'

SELECT * FROM employee WHERE employee_id=101

For Example: - 'ON'

There are two tables employee and employee_details, the matching columns are employee_id.

SELECT * FROM employee 
INNER JOIN employee_details 
ON employee.employee_id = employee_details.employee_id

Hope I have answered your Question. Revert for any clarifications.

7楼
+10

Are you trying to join data or filter data?

For readability it makes the most sense to isolate these use cases to ON and WHERE respectively.

  • join data in ON
  • filter data in WHERE

It can become very difficult to read a query where the JOIN condition and a filtering condition exist in the WHERE clause.

Performance wise you should not see a difference, though different types of SQL sometimes handle query planning differently so it can be worth trying ¯\_(ツ)_/¯ (Do be aware of caching effecting the query speed)

Also as others have noted, if you use an outer join you will get different results if you place the filter condition in the ON clause because it only effects one of the tables.

I wrote a more in depth post about this here: https://dataschool.com/learn/difference-between-where-and-on-in-sql

8楼
0

I think this distinction can best be explained via the logical order of operations in SQL, which is, simplified:

  • FROM (including joins)
  • WHERE
  • GROUP BY
  • Aggregations
  • HAVING
  • WINDOW
  • SELECT
  • DISTINCT
  • UNION, INTERSECT, EXCEPT
  • ORDER BY
  • OFFSET
  • FETCH

Joins are not a clause of the select statement, but an operator inside of FROM. As such, all ON clauses belonging to the corresponding JOIN operator have "already happened" logically by the time logical processing reaches the WHERE clause. This means that in the case of a LEFT JOIN, for example, the outer join's semantics has already happend by the time the WHERE clause is applied.

I've explained the following example more in depth in this blog post. When running this query:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

The LEFT JOIN doesn't really have any useful effect, because even if an actor did not play in a film, the actor will be filtered, as its FILM_ID will be NULL and the WHERE clause will filter such a row. The result is something like:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
194       MERYL       ALLEN      1
198       MARY        KEITEL     1
30        SANDRA      PECK       1
85        MINNIE      ZELLWEGER  1
123       JULIANNE    DENCH      1

I.e. just as if we inner joined the two tables. If we move the filter predicate in the ON clause, it now becomes a criteria for the outer join:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
  AND film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

Meaning the result will contain actors without any films, or without any films with FILM_ID < 10

ACTOR_ID  FIRST_NAME  LAST_NAME     COUNT
-----------------------------------------
3         ED          CHASE         0
4         JENNIFER    DAVIS         0
5         JOHNNY      LOLLOBRIGIDA  0
6         BETTE       NICHOLSON     0
...
1         PENELOPE    GUINESS       1
200       THORA       TEMPLE        1
2         NICK        WAHLBERG      1
198       MARY        KEITEL        1

In short

Always put your predicate where it makes most sense, logically.

9楼
0

Let's consider those tables :

A

id | SomeData

B

id | id_A | SomeOtherData

id_A being a foreign key to table A

Writting this query :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A;

Will provide this result :

/ : part of the result
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+-------+-------------------------+
|/////////////////////////////|
+-----------------------------+

What is in A but not in B means that there is null values for B.


Now, let's consider a specific part in B.id_A, and highlight it from the previous result :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|///////|                         |
|/////////////////////|///////|                         |
|/////////////////////+---+///|                         |
|/////////////////////|***|///|                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Writting this query :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
AND B.id_A = SpecificPart;

Will provide this result :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|/////////////////////|       |                         |
|/////////////////////|       |                         |
|/////////////////////+---+   |                         |
|/////////////////////|***|   |                         |
|/////////////////////+---+---+-------------------------+
|/////////////////////////////|
+-----------------------------+

Because this removes in the inner join the values that aren't in B.id_A = SpecificPart


Now, let's change the query to this :

SELECT *
FROM A
LEFT JOIN B
ON A.id = B.id_A
WHERE B.id_A = SpecificPart;

The result is now :

/ : part of the result
* : part of the result with the specific B.id_A
                                       B
                      +---------------------------------+
            A         |                                 |
+---------------------+-------+                         |
|                     |       |                         |
|                     |       |                         |
|                     +---+   |                         |
|                     |***|   |                         |
|                     +---+---+-------------------------+
|                             |
+-----------------------------+

Because the whole result is filtered against B.id_A = SpecificPart removing the parts B.id_A = NULL, that are in the A that aren't in B

歡迎來到SO。好介紹!:-) - 唐問題2012年11月8日23:36

這更像是REPLACE INTO而不是INSERT INTO ...在DUPLICATE KEY UPDATE,如果你使用觸發器可能會導致問題。您最終將運行刪除和插入觸發器/規則,而不是更新它們。 - cHao 2014年5月15日16:48

10楼
-50

this is my solution.

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID  
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

You must have the GROUP BY to get it to work.

Hope this help.

@Lucian MERGE也更像是一個OLAP操作; 有關說明,請參閱stackoverflow.com/q/17267417/398670。它沒有定義並發語義,大多數使用它進行upsert的人只是在創建bug。 - 克雷格林格2015年5月8日7:56

0
votes
answers
34 views
+10

合併2不等同數據框由多個條件

0

我有2個數據框(工資和投球)。薪水(我們發現球員的薪水)有26'000個參賽項目,Pitching(我們發現球員的統計數據)有19'000項。顯然,賺取某些具體統計數據而不僅僅是薪水是相當複雜的,所以這是可理解的。合併2不等同數據框由多個條件

現在我想添加一個薪金列到Pitching數據框中,但只是匹配的(相同的球員,相同的日期)。我想代碼如

如果球員姓名(來自工資數據框)=球員姓名(來自投球數據框)和年份(來自薪水數據框)=年(從投球數據框),然後在投球數據框中插入薪水,否則寫N/A

然後以這種方式,我可以刪除與N/A的行,並與我想要的數據cleen工作表,可能少一點意見,但一個完整的。

我想是這樣的:

full_join(Salaries,Pitching, by="salary") %>% 
 
    mutate(condition = (Salaries$playerID == Pitching$playerID & Salaries$yearID = Pitching$yearID)) 
 
    
 

 
CombineDf <- merge(y=Salaries, y=Pitching, by=Pitching$playerID) 
 

 
joined = cbind(Salaries[match(names(Pitching), Salaries$playerID),], Pitching) 
 

 
Pitching$Salary <- ifelse(Salaries$playerID >= Pitching$playerID & Salaries$yearID >= Pitching$yearID, Salaries$salary, "N/A")

但遺憾的是沒有做正常工作。如果你能幫我解決這個問題,我會很高興。

非常感謝

+0

*但遺憾的是沒有任何工作正常* ...這是什麼意思?錯誤?不想要的結果? – Parfait

+0

嘿帕爾法斯感謝您的評論。是的,我成爲2 df的長度不一樣的錯誤。 –

沙发
0
1

只需運行一個?merge保持所有x行(即左連接),其中無與倫比的Y列充滿NA:

combine_df <- merge(x=Pitching, y=Salaries, by=c("playerID", "yearID"), all.x=TRUE) 

而且因爲你會去除NA工資行之後,運行merge,兩個數據幀(即內連接)之間完全匹配,沒有明確的all.x可選參數。或者與


left_joindplyr

combine_df <- left_join(x=Pitching, y=Salaries, by = c("playerID", "yearID")) 

與上述相同的理由,通過ID字段兩組之間的完全匹配運行inner_join

+0

非常感謝您的幫助Parfait! C'est Parfait –

0
votes
answers
17 views
+10

SQL左連接:如何從tableB返回最新並由另一個字段分組

0

我一直在嘗試兩天,沒有運氣。SQL左連接:如何從tableB返回最新並由另一個字段分組

我有我的數據庫中以下簡化表:

客戶:

| id | name  | 
| 1 | andrea | 
| 2 | marco | 
| 3 | giovanni | 

訪問:

| id | name_id | date | 
| 1 | 1  | 5000 | 
| 2 | 1  | 4000 | 
| 3 | 2  | 1500 | 
| 4 | 2  | 3000 | 
| 5 | 2  | 1000 | 
| 6 | 3  | 6000 | 
| 7 | 3  | 2000 | 

我想所有的名字與他們的最後訪問日期返回。

起初我

SELECT * FROM customers LEFT JOIN access ON customers.id = 
access.name_id 

只是嘗試,但我得到了7行,而不是3預期。所以I understood I need to use GROUP BY statemet如下所示:

SELECT * FROM customers LEFT JOIN access ON customers.id = 
access.name_id GROUP BY customers.id 

據我所知,GROUP BY使用隨機行聯合收割機。事實上,我有幾個測試無序的訪問日期。

相反,我需要將每個客戶ID與其對應的最新訪問進行分組!如何做到這一點?

+0

我不確定我是否正確理解您希望在這裏實現的目標,但我留下的印象可能會誤解LEFT JOIN的工作原理。如果您擁有像MAX或MIN這樣的聚合功能,則GROUP BY將起作用。你能更好地解釋你期望看到的輸出是什麼? – Stefano

+0

@decadenza。 。 。 「SELECT *」與「GROUP BY」的使用是一個破碎的問題,它已經在最新版本的MySQL中得到了很好的解決。這不是做你想做的事的正確方法。 –

沙发
0
1

您必須從訪問表中獲取最近的日期,並在name_id上??使用group by,然後將此結果與客戶表。以下是查詢:

select c.id, c.name, a.last_access_date from customers c left join 
(select id, name_id, max(access_date) last_access_date from access group by name_id) a 
on c.id=a.name_id; 

這是在sqlfiddle上的DEMO。如果這個說法是正確的

SELECT a.*, c.* 
FROM customers c LEFT JOIN 
    access a 
    ON c.id = a.name_id AND 
     a.DATE = (SELECT MAX(a2.date) FROM access a2 WHERE a2.name_id = a.name_id); 

板凳
0
1

我認爲這是你想達到什麼樣的:

SELECT c.id, c.name, max(a.date) last_access 
FROM customers c 
LEFT JOIN access a ON c.id = a.name_id 
GROUP BY c.id, c.name 

的LEFT JOIN將返回表客戶的所有條目無論是否連接標準(c.id = a.name_id)滿意。這意味着你可能會得到一些NULL條目。

例子:

只需在客戶表中添加一個新行(ID:4,名稱:曼努埃拉)。輸出將有4行,最新的行將是(id:4,last_access:null)

地板
0
1

我的ON子句中使用相關子查詢做到這一點

我需要組每一位客戶ID與其對應的最新的訪問!如何做到這一點?

然後,你可以簡單地做:

??

你不需要customers表,因爲:

  • 所有的客戶都在access,所以left join是沒有必要的。
  • 您不需要customers的列。
0
votes
answers
29 views
+10

SQL查詢在同一個表JOIN給定數據

3

我有以下表和數據:SQL查詢在同一個表JOIN給定數據

CREATE TABLE TEST_TABLE (
    ID NUMBER(6) NOT NULL, 
    COMMON_SEQ NUMBER(22), 
    NAME VARCHAR(20), 
    CONSTRAINT PK_CONST PRIMARY KEY (ID) 
); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1001, NULL, 'Michelle'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1002, NULL, 'Tiberius'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1003, NULL, 'Marigold'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1004, 999, 'Richmond'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1005, 999, 'Marianne'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1006, NULL, 'Valentin'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1007, 888, 'Juliette'); 
INSERT INTO TEST_TABLE (ID, COMMON_SEQ, NAME) VALUES (1008, NULL, 'Lawrence'); 

在此表中的一些記錄被COMMON_SEQ的共同價值(與對方的例子COMMON_SEQ 999涉及里士滿和瑪麗安)。

如何根據給定ID作爲輸入選擇所有名稱?

我試着將表加入自己(當COMMON_SEQ爲空時工作正常)。此示例返回米歇爾記錄:

SELECT T.ID, T.COMMON_SEQ,T.NAME 
FROM TEST_TABLE T 
LEFT JOIN TEST_TABLE T2 ON NOT T.COMMON_SEQ is NULL 
    AND T.COMMON_SEQ=T2.COMMON_SEQ AND T.ID<>T2.ID 
WHERE T.ID=1001 

不過,這並不帶回2個記錄ID 1004這個例子只返回里士滿記錄(但我還需要返回瑪麗安記錄):

SELECT T.ID, T.COMMON_SEQ,T.NAME 
FROM TEST_TABLE T 
LEFT JOIN TEST_TABLE T2 ON NOT T.COMMON_SEQ is NULL 
    AND T.COMMON_SEQ=T2.COMMON_SEQ AND T.ID<>T2.ID 
WHERE T.ID=1004 

當我只提供一個ID值(1004或1005)時,如何改進/重寫查詢以返回Richmond和Marianne記錄?

+0

因爲NULL = NULL是不正確的。 (你可以使用'x不是從y'離開,如果可用的話) – wildplasser

+0

你使用哪個數據庫?甲骨文? – Ramesh

+0

@Ramesh我使用2個數據庫:H2和Oracle。我需要適用於這兩者的通用SQL。 – user1697575

沙发
0
4

你可以使用:

SELECT * 
FROM TEST_TABLE t 
WHERE COMMON_SEQ IN (SELECT COMMON_SEQ 
        FROM TEST_TABLE t1 
        WHERE t1.ID = 1004) 
    OR t.ID = 1004;     

DBFiddle Demo

傳遞相同的參數兩次COMMON_SEQ處理NULL

+1

謝謝!我只是試過了,它會爲我的所有測試用例返回預期的結果! – user1697575

+0

@ user1697575很高興聽到它。 – lad2025

板凳
0
2

試試這個

SELECT COALESCE (ty.id, tx.id) AS id, 
     COALESCE (ty.common_seq, tx.common_seq) AS common_seq, 
     COALESCE (ty.name, tx.name) AS name 
    FROM test_table tx LEFT OUTER JOIN test_table ty 
      ON (tx.common_seq = ty.common_seq) 
WHERE tx.ID = 1004; 

有了這個,你能避免使用IN或EXISTS,這很可能是更好的性能。

+0

謝謝!有用。 – user1697575

+0

@ user1697575 - 很高興幫助 – Ramesh

+0

只是好奇你爲什麼使用COALESCE函數?我不能只使用SELECT ty。* FROM ...? – user1697575

0
votes
answers
42 views
+10

加入到活動記錄codeigniter

-2

您想幫助我嗎?如何將這個sql查詢更改爲活動記錄在笨加入到活動記錄codeigniter

LEFT JOIN MGMETADATA.services d ON d.protocol = e.protocol and d.port = e.s_port 

感謝,

+2

它是你的完整查詢嗎? –

沙发
0
0

請儘量將

$this->db->select('*'); 
$this->db->from('tableE'); 
$this->db->join('tableD','tableD.protocol=tableE.protocol and tableD.port=tableE.s_port','left'); 
$query = $this->db->get(); 
return $query->result(); 

我希望這可以幫助你

板凳
0
0
$this->db->select("*") 
     ->join("MGMETADATA.services as d","d.protocol = e.protocol","left") 
     ->where("d.port = e.s_port") 
     ->get("tablename e") 
     ->result(); //for json format 

注:不要使用「*」選擇所有列,請使用單個列名進行檢索。

0
votes
answers
33 views
+10

如何在SQL Server中使用self Join來在單個表上映射子父項?

1

我有以下表格與子女父母的關係。如何在SQL Server中使用self Join來在單個表上映射子父項?

ID  Title    PageID IsParent ParentID IsActive 
1  Dashboard   2125 True  NULL  True 
2  Site Analytics  22  False  NULL  True 
3  SEO Management  1  NULL  NULL  True 
4  Mail Management  32  NULL  NULL  True 
5  Build Mobile App 3214 NULL  NULL  True 
6  Market Analytics 1321 NULL  NULL  True 
7  Customize   235345 NULL  NULL  True 
8  Reporter   253  NULL  NULL  True 
9  Editor    545  NULL  NULL  True 
10  News Template  45  NULL  NULL  True 
11  Test Menu   0  True  3   True 
NULL NULL    NULL NULL  NULL  NULL 

這裏ParentID定義了父母和孩子之間的關係。例如,在上面的表測試菜單Site Analytics的孩子。我有以下SQL查詢。

SELECT 
     P.ID 
     ,P.Title AS Parent 
     ,C.Title AS Child 
     ,P.PageID 
     ,P.IsParent 
     ,P.ParentID 
     ,P.IsActive 
     FROM [dbo].[ChildParent] P 
     LEFT JOIN [dbo].[ChildParent] C ON P.ID = C.ParentID 

以下是輸出結果。

1 Dashboard   NULL   2125 1  NULL 1 
2 Site Analytics  NULL   22  0  NULL 1 
3 SEO Management  Test Menu  1  NULL NULL 1 
4 Mail Management  NULL   32  NULL NULL 1 
5 Build Mobile App NULL   3214 NULL NULL 1 
6 Market Analytics NULL   1321 NULL NULL 1 
7 Customize   NULL   235345 NULL NULL 1 
8 Reporter   NULL   253  NULL NULL 1 
9 Editor    NULL   545  NULL NULL 1 
10 News Template  NULL   45  NULL NULL 1 
11 Test Menu   NULL   0  1  3  1 

基本上,我想實現的是:

1 Dashboard   NULL   2125 1  NULL 1 
2 Site Analytics  NULL   22  0  NULL 1 
3 SEO Management  NULL   1  NULL NULL 1 
4 Mail Management  NULL   32  NULL NULL 1 
5 Build Mobile App NULL   3214 NULL NULL 1 
6 Market Analytics NULL   1321 NULL NULL 1 
7 Customize   NULL   235345 NULL NULL 1 
8 Reporter   NULL   253  NULL NULL 1 
9 Editor    NULL   545  NULL NULL 1 
10 News Template  NULL   45  NULL NULL 1 
11 Test Menu   SEO Management 0  1  3  1 
沙发
0
1

你向後做。

FROM [dbo].[ChildParent] P 
    LEFT JOIN [dbo].[ChildParent] C ON P.ParentID = C.ID 
板凳
0
1

在查詢試試這個小變化:

SELECT 
     P.ID 
     ,P.Title AS Parent 
     ,C.Title AS Child 
     ,P.PageID 
     ,P.IsParent 
     ,P.ParentID 
     ,P.IsActive 
     FROM [dbo].[ChildParent] P 
     LEFT JOIN [dbo].[ChildParent] C ON isnull(P.ParentID, P.ID) = c.id and C.ParentID is not null 
+0

此查詢不工作,因爲我期望:) –

+0

@KiranShahi:你能提供更多的細節?它給了多少不同的結果? –

+0

這將返回所有子列NULL –

0
votes
answers
12 views
+10

刪除內連接行

1

我創建了兩個表,簡單註釋評論者,並將它們連接到INNER JOIN刪除內連接行

  • Simplecomments是每個評論者的每一個細節,涉及其評論,reg_date,commentorid等...
  • 評論者是評論者的個人信息有下列:ID,姓名,電子郵件..

我已經加入他們成功,但我很難從聯合表中刪除。

我想使它像這樣的邏輯:

  1. 如果有一個名爲--let評註的最後一行說A--然後同時刪除他/她的評論詳細信息和他/她自己從桌子上。

  2. 否則如果A已評論很多次,有不同的意見,刪除了他/她的意見的細節,但讓他/她的個人信息仍然因爲A有沒有其他意見。

這是我如何做了它:

if (!empty($_POST["delete"])) 
{ 
    foreach ($_POST["delete"] as $key => $value) 
    { 
     $resultid = $conn->query("SELECT commentorid FROM `simplecomments` WHERE id=".$value); 
     $rowid = $resultid->fetch_assoc(); 

     $outputdelete = $rowid["name"] . " has been deleted" . "<br>"; 

     $deletedname = $deletedname.$outputdelete; 
     $RES = mysql_num_rows($resultid); 
     $counter = 0; 
      while($row = $RES) 
      { 
       //IF IT'S LAST ROW, DELETE COMMENTOR AND HIS/HER COMMENTDETAILS 
       if(++$counter == $results) { 
        $resultid = $conn->query("DELETE FROM `commentor`"); 
       } 
       //ELSE JUST DELETE HIS/HER COMMENTDETAILS, LET HIS/HER INFO REMAIN 
       else{ 
        $resultid = $conn->query("DELETE FROM `simplecomments` WHERE id=".$value); 
       } 
      } 
    } 

} 

但是代碼將無法正常工作。我得到一個錯誤:

Warning: mysql_num_rows() expects parameter 1 to be resource [..]...

+0

請發佈你得到的結果和你期望得到的結果 –

+0

@BrianDewhirst我得到一個錯誤..「警告:mysql_num_rows()期望參數1是資源,」.. 我在最後兩段中的問題中已經說明了我希望如何。 –

+0

https://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row -expects-parameter-1 –

沙发
0
2

考慮運行DELETE...INNER JOINDELETE與子查詢條件語句,避免PHP查詢獲取循環與if/else的邏輯似乎是以下幾點:

  1. 刪除任何評註者的個人資料,如果評論他/她只有一條評論
  2. 如果他/她有多個(即多個)評論,則只刪除評論者的評論。

是的,所有三個DELETE可以在所有ID上同時運行,因爲互斥條件放在前兩個和最後一個之間。因此,前兩個影響行或最後一個影響每次迭代的行。未受影響的人將從兩個表中刪除零行。

此外,simplecomments記錄首先刪除,因爲這個表可能與評註外鍵約束,因爲它一個一對多的關係。最後,下面假設評論 ID傳遞到循環(不是評論或 id)。

PHP(使用參數,假定$ conn是一個mysqli的連接對象)

foreach ($_POST["delete"] as $key => $value) { 

    // DELETE COMMENTS AND THEN PROFILE FOR COMMENTORS WITH ONE POST  
    $sql = "DELETE FROM `simplecomments` s 
      WHERE s.id = ? 
      AND (SELECT COUNT(*) FROM `simplecomments` sub 
        WHERE sub.commentorid = s.commentorid) = 1"; 
    $stmt = $conn->prepare($sql); 
    $stmt->bind_param("i", $value); 
    $stmt->execute(); 
    $stmt->close(); 

    $sql = "DELETE c.* FROM `simplecomments` c 
      INNER JOIN `simplecomments` s ON s.commentorid = c.id 
      WHERE s.id = ? 
      AND (SELECT COUNT(*) FROM `simplecomments` sub 
        WHERE sub.commentorid = s.commentorid) = 1"; 
    $stmt = $conn->prepare($sql); 
    $stmt->bind_param("i", $value); 
    $stmt->execute(); 
    $stmt->close(); 


    // DELETE COMMENTS FOR COMMENTORS WITH MULTIPLE POSTS BUT KEEP PROFILE 
    $sql = "DELETE FROM `simplecomments` s 
      WHERE s.id = ? 
      AND (SELECT COUNT(*) FROM `simplecomments` sub 
        WHERE sub.commentorid = s.commentorid) > 1";  
    $stmt = $conn->prepare($sql); 
    $stmt->bind_param("i", $value); 
    $stmt->execute(); 
    $stmt->close(); 
} 

或者,對於DRY-ER方法中,循環的SQL語句中的陣列:

$sqls = array(
      0 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1", 
      1 => "DELETE c.* FROM `simplecomments` c INNER JOIN `simplecomments` s ON s.commentorid = c.id WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) = 1", 
      2 => "DELETE FROM `simplecomments` s WHERE s.id = ? AND (SELECT COUNT(*) FROM `simplecomments` sub WHERE sub.commentorid = s.commentorid) > 1" 
     ); 

foreach ($_POST["delete"] as $key => $value) { 
    foreach($sqls as $sql) { 
     $stmt = $conn->prepare($sql); 
     $stmt->bind_param("i", $value); 
     $stmt->execute(); 
     $stmt->close(); 
    } 
} 
+0

非常感謝你的男人,感激它!然而,c代表評論者,s代表簡單評論和sub? –

+1

太棒了!樂意效勞。是的,這些字母是實際表格的表別名,有助於縮短查詢時間,並有助於在使用相同表格的子查詢和外部查詢之間進行關聯。 – Parfait

0
votes
answers
19 views
+10

SQL需要羣體

1

我想從1個表組唯一的行可能在表2SQL需要羣體

多行如果我讓這個查詢:

SELECT c.`lastname`,a.`city` 
FROM `customer` c 
LEFT JOIN `visit` a ON (c.`id` = a.`id`); 

我得到的結果是這樣的:

lastname |city 
-------- |---- 
DOE  |Paris 
DOE  |Miami 
Technical|Toronto 
Technical|d 
M  |toronto 
mavya |TORONTO 
lname |NULL 

customervisit表格也有id我加入,但不顯示在這裏)。

如何獲得如下結果?(我可以得到SQL返回內部結果數組?)

lastname |city 
-------- |---- 
DOE  |Paris,Miami 
Technical|Toronto,d 
M  |toronto 
mavya |TORONTO 
lname |NULL 
+2

什麼[標籤:RDBMS]您使用的? – Mureinik

沙发
0
1

根據你的語法,我猜測你正在使用MySQL(或SQLite的)。

在這兩種,你可以使用GROUP_CONCAT()

SELECT c.lastname, GROUP_CONCAT(v.`city`) 
FROM customer c LEFT JOIN 
    visit v 
    ON c.id = v.id 
GROUP BY c.lastname; 
+0

我知道很簡單。並且謝謝我現在有足夠的聲望來提高答案的答案 – user3247130

0
votes
answers
18 views
+10

「TypeError:只能加入一個迭代」當試圖將列表項添加到字符串

2

我有一個類有兩個列表作爲變量。它有一個對象,它應該將列表中的每個元素添加到(相當長的)字符串中,然後返回到主程序,最終通過打印。我用for循環遍歷列表並使用.join()將每個對象添加到字符串,但我得到一個TypeError:「只能加入一個迭代」。「TypeError:只能加入一個迭代」當試圖將列表項添加到字符串

列表包含在餐廳購買的東西的價格,所以只是浮動數字。

Class A: 

    def __init__(self, etc.): 
     self.__foods = [] 
     self.__drinks = [] 

然後我有一個對象,它應該打印一張預先確定的收據,然後作爲一個字符串傳遞給主程序。

Class A: 
    ... 

    def create_receipt(self): 
     food_price_string = "" # What is eventually joined to the main string 
     food_prices = self.__foods # What is iterated 

     for price in food_prices: 
      food_price_string.join(price) # TypeError here 
      food_price_string.join("
") # For the eventual print 

這裏就是我得到的類型錯誤 - 該程序拒絕加入「價格」變量上面創建的字符串。我應該做同樣的事情的飲料價格也一樣,兩者將被加入到字符串的其餘部分:

沙发
0
4

有兩個問題在這裏:

  1. str.join不會改變字符串(字符串是不可變的),它返回一個新的字符串;和
  2. 它需要輸入一個可迭代的連接在一起的字符串,而不是將單個字符串加在一起。

事實上,food_prices是迭代不要緊,因爲你使用for循環中,price s爲的food_prices的元素,這樣的話你加入列表的一個項目。

可以改寫程序,如:

def create_receipt(self): 
    food_prices = self.__foods 
    food_price_string = '
'.join(str(price) for price in food_prices) food_price_string += '
' # (optional) add a new line at the end 
    # ... continue processing food_price_string (or return it)
+0

注:即使一個字符串項是Python中的序列(迭代器)。如果列表項是字符串,則不會有TypeError(儘管問題中的「」.join「用法仍然是錯誤的)。要打印食品價格,每行一個:'print(* food_prices,sep =' n')'。手動格式化:'s =' n'.join(map(str,food_prices))+' n'' – jfs