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: 6

23
votes
answers
26 views
+10

如何从Oracle中的日期中提取月份号

我有 ID_BB_SECURITY 列,它是数字数据类型,但日期值存储在此列中,例如'20190801'

我想要从这个字段获取月份数,例如8月日期我想得到8。

我尝试了下面的查询,但它抛出错误'文字不匹配':

 从BT_EXPORT中选择to_number(to_date(ID_BB_SECURITY),'mm') 
    
        
沙发
+60
+50
/ pre>

或者您可以使用子字符串:

 从BT_EXPORT中选择to_number(substr(ID_BB_SECURITY,5,2));   

这些采用固定的一致格式,在使用错误的数据类型时,这始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份)  

这些假定采用固定的一致格式,在使用错误的数据类型时始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份)  

这些假定采用固定的一致格式,在使用错误的数据类型时始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份) 
     
			
        

您无需提供格式模型; 如果不这样做,那么TO_DATE将使用NLS_DATE_FORMAT会话参数作为默认格式模型,如果这不匹配,那么您将得到相应的错误。但是,您应该提供显式格式模型,因为每个用户都可以修改自己的会话参数(并且默认值在不同的国际地区可能不同),因此您不能依赖不同用户之间的默认值或一致性。 - MT0 8月29日10:57

+50

如果你有一个数字,你可以使用算术来提取月份:

  select dual(floor(20190801/100),100)来自dual;  
     
			
        

倾向于成为唯一不依赖丑陋的字符串操作的答案。 - Tim Biegeleisen 8月29日10:43

一般工作但它也适用于错误的数字,如mod(floor(20191301/100),100)或mod(floor(20190230/100),100) - 它取决于是否接受这些值的要求。 - Wernfried Domscheit 8月29日10:52

@TimBiegeleisen:赞成成为唯一使用丑陋数字操作的答案。:-) - Bob Jarvis 8月29日11:22

+40

您可以尝试将数字日期转换为字符串,然后提取第5和第6个字符:

  SELECT SUBSTR(TO_CHAR(ID_BB_SECURITY),5,2)AS mm FROM BT_EXPORT;   

但是,使用正确的日期列会更好。然后,你可以使用一个不那么严苛的方法,例如:

  SELECT TO_CHAR(ID_BB_SECURITY,'mm')AS mm  - 假设日期来自BT_EXPORT;  
     
			
        
+30
 从双 中选择to_number(to_char(to_date('20190801','yyyymmdd'),'mm'))
     
			
        
+20

 从双 中选择提取(来自to_date('20190801','yyyymmdd')的月份)
     
			
        
+20

尝试这个

 从BT_EXPORT选择提取(月份来自to_date(ID_BB_SECURITY,'YYYYMMDD))  

这个转换号码到目前为止提取月份。

怎么样“避免异步void。返回一个像公共异步TaskDownloadAsync(字符串路径,字符串[]名称)的任务,这样你就可以等待那个方法” - Uzay 2天前

我没有异步无效。它是异步main中的异步方法。 - Murdock 2天前

可能不同的加密或zip方法 - ralf.w. 2天前

我可以证实这种行为。通过MS Azure Storage Explorer下载我的文件在01:09.87下载,但是通过DownloadToFileAsync方法,它需要03:04:01。也许他们将下载分成Azure存储资源管理器中的块,然后进行并行下载? - 贾里德2天前

+10

您的日期列的值存储格式为“yyyymmdd”,其中

  • yyyy是年份
  • mm月份
  • < p> dd当天为了返回月份的数值(mm),我们可以按如下方式进行:

    1:首先使用to_date将值从数字转换为日期(20190801,' yyyymmdd')

    2:使用to_date运算符to_char获取月份(to_date(20190801,'yyyymmdd'),'mm')

我还在使用旧版本的Storage Explorer(1.4.1)。所以它不能成为制造差异的azcopy,但我会给你的建议一个去。 - 昨天默多克

@Murdock,请在你的帖子中指定存储资源管理器的版本:) - Ivan Yang昨天

0
votes
answers
48 views
+10

最好的辦法讓SQL日期比較與SYSDATE - 增加30天DUE_DATE

0
SELECT * 
FROM reciepts 
WHERE Remarks = 'PAID' and DATEADD(day,30,Due_date) =<SYSDATE 

顯然我不能讓這幹活只是想表明已標記支付了我的發言表,然後將表看看他們是否已經過了截止日期,從截止日期起30天。最好的辦法讓SQL日期比較與SYSDATE - 增加30天DUE_DATE

每當我嘗試比較sysdate值時,我的sql語法錯誤就會觸發。

+0

請提供樣本數據和預期結果。 –

+0

說你得到一個錯誤,然後不全面引用它是沒用的。發表它。 –

+0

我想要獲取過去30天內具有備註=付款和due_date的所有數據。 –

沙发
0
1

如果我理解正確,你想做什麼,然後在MS Access語法是:

SELECT * 
FROM reciepts 
WHERE Remarks = "PAID" and DATEADD("d", 30, Due_date) < date(); 

這是更好地把職能的「常數」的習慣來獲得,而不是列:

SELECT * 
FROM reciepts 
WHERE Remarks = "PAID" and Due_date < DATEADD("d", -30, date()); 
+0

@ErikvonAsmuth。 。 。我完全同意。好點子。 –

+0

非常感謝這段代碼工作 –

+0

Imma現在注意事情如何工作在SQL日期的東西 –

0
votes
answers
47 views
+10

POSTGRES:如何做循環和如何加入循環內?

0

SQL新手在這裏。POSTGRES:如何做循環和如何加入循環內?

我有兩個表像這些

表:訂單

enter image description here

CREATE TABLE orders (
ID serial NOT NULL PRIMARY KEY, 
data json NOT NULL 
); 

INSERT INTO orders (data) 
VALUES 
(
'{"customer":"John Doe","items":[{"1234":{"attributes":{"name":"parent"}},"id":"1234","isPhone":true,"items":[{"5678":{"attributes":{"name":"child"}},"id":"5678","isPhone":false}]},{"9012":{"attributes":{"name":"parent"}},"id":"9012","isPhone":true,"items":[{"3456":{"attributes":{"name":"child"}},"id":"3456","isPhone":false}]}]}' 
); 

INSERT INTO orders (data) 
VALUES 
(
'{"customer":"John Doe","items":[{"7890":{"attributes":{"name":"parent"}},"id":"7890","isPhone":true,"items":[{"0987":{"attributes":{"name":"child"}},"id":"0987","isPhone":false}]},{"6543":{"attributes":{"name":"parent"}},"id":"6543","isPhone":true,"items":[{"2109":{"attributes":{"name":"child"}},"id":"2109","isPhone":false}]}]}' 
); 

表:積壓

enter image description here

CREATE TABLE backlog (
ID serial NOT NULL PRIMARY KEY, 
data json NOT NULL 
); 

    INSERT INTO backlog (data) 
VALUES 
('{"id":"5678","isAvailable":true,"order_id":1}'), 
('{"id":"9012","isAvailable":true,"order_id":1}'), 
('{"id":"3456","isAvailable":false,"order_id":1}'), 
('{"id":"7890","isAvailable":false,"order_id":2}'), 
('{"id":"0987","isAvailable":false,"order_id":2}'), 
('{"id":"6543","isAvailable":false,"order_id":2}'), 
('{"id":"2109","isAvailable":false,"order_id":2}'), 
('{"id":"1234","isAvailable":true,"order_id":1}') 

現在我需要每個order.data.items陣列裏面循環遞歸以獲得兩家母公司&子項的id和做基礎上,Backlog.data.id聯接積壓表

最終輸出喜歡被

enter image description here

如果我沒有正確地解釋我的需求,請評論我會努力讓儘可能多詳細。

在此先感謝。

+2

如果這是一個SQL數據庫,我會建議你改變你的數據結構。這看起來更像NoSQL數據庫。 –

+0

postgres有jsonb支持,所以去了SQL有nosql支持。 – Sathish

+1

僅僅因爲Postgres具有相當不錯的JSON/NoSQL支持,並不意味着規則或適當的數據庫設計應該被拋棄。不要對所有東西都使用JSON。使用正確的標準化模型,這與加入兩個表格一樣簡單。見例如這裏:https://blog.2ndquadrant.com/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/ –

沙发
0
0

你可以用子查詢(或CTE)和generate_series()來做到這一點。

select baz from (select o.data->'items'->generate_series(0, json_array_length(o.data->'items') - 1)->>'id' as foo, b.data->>'id' as bar, b.data as baz from orders as o, backlog as b) as t where foo = bar; 

這將返回

      baz       
----------------------------------------------------- 
{"id": "9012", "order_id": 1, "isAvailable": true} 
{"id": "7890", "order_id": 2, "isAvailable": false} 
{"id": "6543", "order_id": 2, "isAvailable": false} 
{"id": "1234", "order_id": 1, "isAvailable": true} 
(4 rows) 

解構和理解我的查詢被留作練習。

和閱讀文檔: https://www.postgresql.org/docs/current/static/functions-json.html

+0

我很可能沒有得到您父母和孩子的所有要求,但我希望這能讓您走上正確的道路。 –

0
votes
answers
51 views
+10

ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

0

當我做插入給我這個錯誤:ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

ORA-02291: integrity constraint (H00300581.FK_CLASSES) violated - parent key not found

這種錯誤發生的所有鑲入登記表。我需要幫助,請嘗試爲我解決。

它來自表中的外鍵嗎?這可能是因爲我嘗試了一切,但它沒有奏效。

創建表

drop table g3_trainers CASCADE CONSTRAINTS; 
drop table g3_classes CASCADE CONSTRAINTS; 
drop table g3_members CASCADE CONSTRAINTS; 
drop table g3_registration CASCADE CONSTRAINTS; 

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

CREATE TABLE G3_CLASSES 
(
CLASS_NUM VARCHAR(6) NOT NULL, 
TRAINER_ID VARCHAR(6) NOT NULL, 
CLASS_TITLE VARCHAR(50) NOT NULL, 
START_DATE DATE NOT NULL, 
END_DATE DATE NOT NULL, 
COST NUMBER(6) NOT NULL, 
PRIMARY KEY (CLASS_NUM), 
CONSTRAINT fk_TRAINERS FOREIGN KEY(TRAINER_ID) REFERENCES G3_TRAINERS(TRAINER_ID) 
); 

CREATE TABLE G3_MEMBERS 
(
MEMBER_ID VARCHAR(6) NOT NULL, 
MEMBER_NAME VARCHAR(50) NOT NULL, 
MEMBER_AGE NUMBER(2) NOT NULL, 
GENDER VARCHAR(10) NOT NULL, 
PHONE_NUMBER VARCHAR(10) NOT NULL, 
CHECK (regexp_like (PHONE_NUMBER,'^(d{3}-d{3}-?d{4}|d{10})$')), 
CHECK (MEMBER_AGE>=17), 
CHECK(MEMBER_AGE NOT LIKE '%[^a-zA-Z]%'), 
PRIMARY KEY (MEMBER_ID) 
); 

CREATE TABLE G3_REGISTRATION 
(
REGISTRATION_ID VARCHAR(6) NOT NULL, 
MEMBER_ID VARCHAR(6) NOT NULL, 
AMOUNT_TO_PAY NUMBER(6) NOT NULL, 
CLASS_NUM VARCHAR(6) NOT NULL, 
PRIMARY KEY (REGISTRATION_ID), 
CONSTRAINT fk_MEMBERS FOREIGN KEY(MEMBER_ID) REFERENCES G3_MEMBERS(MEMBER_ID), 
CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM) 
); 

創建序列

CREATE SEQUENCE G3_TRAINER_Seq 
START WITH  10001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_MEMBER_Seq 
START WITH  20001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_CLASSE_Seq 
START WITH  30001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_REGISTRATIONS_Seq 
START WITH  40001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

訓練數據

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Ahmed', 'Eygpt', '2/3/1988', 'Body Building', 'I describe my self as an active guy i workout everyday and thats why i desided to train people to be good as me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'John', 'Canada', '4/6/1987', 'Crossfit', 'I like being healthy and work as much as i can to be fit and to have a great life'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Sara', 'Canada', '9/1/1990', 'Yoga', 'I like interacting with poeple, and to teach them how yoga will be a very good source of flexing and relaxing your body'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Jun', 'China', '12/4/1981', 'Crossfit', 'I describe my self as an daily working guy i like my job because i get to know more about people and i teach many people how to be fit'); 

    INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mike', 'Ohio', '2/22/1981', 'Body Building', 'Being strong and big is what i am aiming for, i like keeping my diet healthy and to train poeple to be healthy and big like me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mia', 'Germany', '12/12/1987', 'Yoga', 'I describe myself as a hard working i love my job and i love my members'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Essam', 'UAE', '1/5/1980', 'Body Building', 'I am a hard worker and i like to train my members and i want them to be healthy and strong'); 

類的數據

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/12/2017', '1/1/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/2/2018', '1/3/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10002, 'Body Weight Class', '2/1/2018', '2/2/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '3/2/2018', '3/3/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '5/5/2018', '5/6/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/2/2018', '10/3/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/4/2018', '10/5/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '12/20/2017', '1/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '2/20/2018', '3/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '4/20/2018', '5/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10006, 'Yoga Class', '12/28/2017', '1/28/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/3/2018', '10/4/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/5/2018', '10/6/2018', 1150); 

成員數據

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Mohammed', '21', 'Male', '0502202212'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Afra', '28', 'Female', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Essa', '30', 'Male', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Ahmed', '19', 'Male', '0563020300'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Alex', '25', 'Female', '0521022932'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulla', '24', 'Male', '0504493393'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulrahman', '20', 'Male', '0502292290'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Olivia', '21', 'Female', '0561020200'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Omar', '30', 'Male', '0502503323'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nasser', '29', 'Male', '0504334439'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Sophia', '22', 'Female', '0561616316'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nadia', '21', 'Female', '0507797786'); 

註冊數據

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 1150, 30010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 1150, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 1150, 30009); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 1150, 30008); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 950, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 1150, 30009); 
+0

您的腳本有太多錯誤。有幾個地方丟失了逗號。您在插入語句中使用的序列名稱中存在拼寫錯誤。首先解決這些問題。 –

+0

@Kaushik Nayak我修復了一些小問題,我如何添加代碼? –

+0

@Kaushik Nayak我編輯它看看它 –

沙发
0
0

commas缺少的大多數語句,如:

between 'Eygpt' & '12/3/1988' in :

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, 
SPECIALITY, DESCRIPTION) VALUES (G3_TRAINERS_Seq.nextVal, 'Ahmed', 
'Eygpt' '12/3/1988', 'Body Building', 'I describe my self as an active 
guy i workout everyday and thats why i desided to train people to be 
good as me'); 

OR

between 'Weight Lifting Class' & '1/12/2017'

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSES_Seq.nextVal, 10001, 'Weight Lifting Class' '1/12/2017', '1/1/2018', 1150); 

你有CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM)爲約束

G3_CLASSES_Seq序列填充g3_classes表的class_num列,應該是方便,fk_CLASSES FOREIGN KEY,但你有另一個序列G3_CLASSE_Seq開始30000。順便說一句,現在看來,這應該與30001

i.e. Pay attention that G3_CLASSE_Seq and G3_CLASSES_Seq are different.

的方式啓動,刪除空格在column names之間create sentences類似以下內容:

爲例,其轉換

CREATE TABLE G3_TRAINERS 
(

TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 

PRIMARY KEY (TRAINER_ID) 

); 

對此:

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

An d在以下語句中存在另一個問題(300010

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010);

+0

非常感謝,解決了我的其他問題之一,但我發佈了主要問題 –

+0

@abdulrahmanarab請看我最後編輯的外鍵和序列。 –

+0

@abdulrahmanarab我認爲,問題不是使用'G3_CLASSE_Seq'(你應該使用這個,從30001開始)。您正在使用'G3_CLASSES_Seq'。 –

0
votes
answers
48 views
+10

顯示作者姓名,書名爲誰寫的多本書籍

-4
CREATE TABLE Book_Master(
Book_code NUMBER(10) PRIMARY KEY, 
Book_name VARCHAR2(50) NOT NULL, 
Book_pub_year NUMBER, 
Book_pub_author VARCHAR2 (50) NOT NULL); 

INSERT INTO book_master VALUES(10000001,'Let Us C++',2000,'Yashavant Kanetkar'); 

INSERT INTO book_master VALUES(10000002,'Mastersing VC++',2005,'P.J Allen'); 

INSERT INTO book_master VALUES(10000003,'JAVA Complete Reference',2004,'H.Schild'); 

INSERT INTO book_master VALUES(10000004,'J2EE Complete Reference',2000,'H. Schild'); 

INSERT INTO book_master VALUES(10000005,'Relational DBMS',2000,'B.C. Desai'); 

INSERT INTO book_master VALUES(10000006,'Let Us C',2000, 'Yashavant Kanetkar'); 

INSERT INTO book_master VALUES(10000007,'Intoduction To Algorithams',2001,'Cormen'); 

INSERT INTO book_master VALUES(10000008,'Computer Networks',2000,'Tanenbaum'); 

INSERT INTO book_master VALUES(10000009,'Introduction to O/S',2001,'Millan'); 
+3

認真對待作業。顯示一些努力。 –

沙发
0
0
select Book_pub_author, Book_name 
    from book_master 
where Book_pub_author in (select Book_pub_author 
           from book_master 
          group by Book_pub_author 
          having count(1)>1) 
order by Book_pub_author, Book_name 
+0

Thx爲您提供幫助 –

+0

@AbhiramVarma歡迎您。 –

板凳
0
0

這些作者還可以使用內部聯接,而不是IN子句

Select Book_pub_author 
    , Book_name 
from book_master b 
INNER JOIN (
    select Book_pub_author 
    from book_master 
    group by Book_pub_author 
    having count(1)>1 
) t on t.Book_pub_author = b.Book_pub_author 

anfd您可以獲取所有的書在同一行中使用group_concat

Select Book_pub_author 
    , group_concat(Book_name) 
from book_master b 
INNER JOIN (
    select Book_pub_author 
    from book_master 
    group by Book_pub_author 
    having count(1)>1 
) t on t.Book_pub_author = b.Book_pub_author 
group by Book_pub_author 
+0

Thx爲您的幫助 –

+0

@AbhiramVarma如果其中一個答案是正確的,請選擇一個你喜歡的,並將其標記爲已接受...看看這裏如何 http://meta.stackexchange.com/questions/5234/how-不接受-的回答工作 – scaisEdge

0
votes
answers
30 views
+10

SQL Server - 從表中選擇一個特定的ID

0

我會解釋我迄今爲止所做的事情,並在最後問我的問題。SQL Server - 從表中選擇一個特定的ID

首先,我建立了我的外部&內部變量(注意@EDelivery是包含在列FoodIDQuantity表類型):

@EDelivery DeliveryTblType readonly 
Declare @NumberOfMinutes smallint, @FoodWeight int, @WeightCapacity int, 
@DroneID int, @TimeOfWeek varchar 

然後我讀這,給我的所有項目的總重量訂購併在承重能力宣讀了DronesTbl

select @FoodWeight = SUM(FoodWeight * Quantity) 
from @EDelivery as del 
inner join Foods on del.FoodID = Foods.FoodID 

select @WeightCapacity = Drones.WeightCapacity 
from dbo.Drones 

我的問題是我該如何選擇具體的無人機比較其weightcapacity到TH e食品總重量

+0

您不使用MySQL,MySQL不支持GETDATE和throw等函數。我已將MySQL標記更改爲SQL服務器標記 –

+0

感謝您告知我 –

沙发
0
0

您需要根據@droneid值設置變量@WeightCapacity。現在,你將這個變量設置爲無人機表中的最後一個無人機。

如果你改變你的第二個查詢到下面的內容那麼它應該工作。

select @WeightCapacity = 
    Drones.WeightCapacity 
     from dbo.Drones 
     where DroneID [email protected] 
+0

我不知道爲什麼我沒有看到這個,感謝您的幫助 –

+0

當然,不客氣。 – Sunil

0
votes
answers
48 views
+10

如何比較2 SQL表差異

0

下表給出的,我怎麼能有2個表給該T143具有每行重複的事實之間的區別:如何比較2 SQL表差異

T001.CODE    T143.CODE 
----     ----- 
A1      A1 
         A1 
A2      A2 
         A2 
A3    

A4      A4 
         A4 

結果應該是A3,它只有出現在T001中 - 我還需要顯示T143中的所有列。

這裏是我做的遠,但數據的手動檢查之後出現錯誤:

SELECT CODE FROM T001 
EXCEPT 
SELECT CODE FROM T143 

感謝

+2

你能顯示你當前得到的結果嗎? –

+2

用您正在使用的數據庫標記您的問題。 –

+0

如果你的結果應該是A3,你的查詢似乎在PostgreSQL和SQLite3下都可以使用。 – tonypdmtr

沙发
0
0

你總是可以做到這一點使用exists

select t1.code 
from t001 t1 
where not exists (select 1 from t143 t2 where t2.code = t1.code); 

如果您還想要t2的列,然後使用union all來獲取它們。

如果你想兩個表中的所有代碼,只是做:

select t1.code 
from t001 
union -- on purpose for duplicate removal 
select t2.code 
from t143; 
+0

他'還需要顯示來自T143的所有列。' – Turo

板凳
0
0

完全外部聯接應該做的伎倆

SELECT DISTINCT COALESCE(T001.CODE,T143.CODE), T143.* 
FROM T001 FULL OUTER JOIN T143 
ON T001.CODE = T143.CODE 
WHERE T001.CODE IS NULL OR T143.CODE IS NULL 

編輯:由於DISTINCT在標籤中提到,我將它添加到查詢中

+0

似乎正在工作,我用T001。*而不是T143。*爲了顯示所有列 - 它有意義嗎? –

+0

這不是你說的問題,但如果它適合你的需求... – Turo

0
votes
answers
33 views
+10

如何在PHP和MySQL中爲不同的登錄用戶分配不同範圍的聯繫人

-1

我正在嘗試在php和mysql中創建一個簡單的CRM。但卡住了一部分。 我有一張可以同時登錄的用戶表,並且一次訪問聯繫人表。但是我想給他們分配不同的數據範圍。例如:如果「」,「」 &「ç」的所有日誌,在相同的時間和訪問前端表「聯繫人」,我想
」至顯示數據來自1-500
'B' - 501-1000。 &
ç」 - 1001-1500如何在PHP和MySQL中爲不同的登錄用戶分配不同範圍的聯繫人

如果有人能幫助我在正確的方向如何做到這一點,我會很感激。

+0

這是一個非常普遍的問題......你應該嘗試一些事情,然後回來更具體的問題。一般來說,在這裏不要求別人解決一般的編程問題。你需要先嚐試自己解決它。 – Serge

+0

我用COUNT和LIMIT嘗試了不同的方法,並加入了表格但沒有運氣。我只想要一些指導繼續。如果我們迷路了,我們一般不會問路嗎? – Tarun

+0

必須知道更多詳細信息,例如您如何確定每個人獲得500是否因爲有1500/3個登錄人員?你只是按照字母順序分配它們,這些東西必須在任何人試圖幫助你之前定義。 –

沙发
0
0

終於找出來了。

我在默認值爲'0'的聯繫人表中創建了名爲'assigned_to'的三個輸入字段和一個列。 字段是 -

  1. 爲僱員ID選擇字段
  2. 分鐘(輸入[類型=文本])範圍字段
  3. 最大(輸入[類型=文本])範圍字段

我使用查詢填充了MIN範圍和MAX範圍字段

select MIN(id) as minid from contacts_table WHERE assigned_to=0 
select MAX(id) as maxid from contacts_table WHERE assigned_to=0 

然後,在表單發佈後,我插入了employee_id在聯繫人表的assigned_to列中。

<?php if (isset($_POST['assign'])) { 
     $sel_emp = $_POST['sel_emp']; 
     $min_range = $_POST['min_range']; 
     $max_range = $_POST['max_range']; 
     $sqli = "UPDATE contacts_table SET `assigned_to`='$sel_emp' WHERE `assigned_to`='0' AND id BETWEEN '$min_range' AND '$max_range'"; 
     if (mysqli_query($conn, $sqli)) { 
      ?> 
      <script> 
       $("#msg").html("Assigned"); 
       $("#msg").css('color', 'green'); 
      </script> 
      <?php 
     } else { 
      echo mysqli_error($conn); 
     } 
     } ?> 

因此,基本上,執行上述主查詢所述操作是

UPDATE leads SET `assigned_to`='251' WHERE `assigned_to`='0' AND id BETWEEN '1' AND '500' 

此更新assigned_to值與EMPLOYEE_ID是251

如果任何人都能夠提供更好的解決方案。我會非常感謝。

0
votes
answers
35 views
+10

MySQL的:獲取匹配的所有行的數量,以及與結果集

0

如果你看一下my fiddle,你可以看到,我運行一個查詢,並試圖讓所有匹配行的計數,結果集一起。MySQL的:獲取匹配的所有行的數量,以及與結果集

我想要得到的相匹配的所有行計數,雖然我可能只返回結果集其中的一小部分,因爲我在做分頁與LIMITOFFSET

我的查詢出了什麼問題?

SELECT item.*, COUNT(item.id) AS _count 
FROM item 
JOIN user 
    ON user.username = "Lansana" 
    AND user.deleted_at IS NULL 
JOIN user_item 
    ON user_item.item_id = item.id 
    AND user_item.user_id = user.id 
    AND user_item.deleted_at IS NULL 
WHERE item.deleted_at IS NULL 
GROUP BY item.id 
ORDER BY item.id DESC 
沙发
0
1

您所做的是在聚合查詢中將您的查詢。在大多數數據庫(以及最近的MySQL版本),你會得到一個錯誤 - 因爲select還沒有在group by未聚集列。

沒有group by恰好返回一行全部聚集查詢。

如果你想要的行數,然後用CALC_FOUND_ROWS(記錄here):

SELECT SQL_CALC_FOUND_ROWS item.* 
. . . ; 

然後發出另一個查詢:

SELECT FOUND_ROWS(); 
+0

發現的行,是不是我要找的。我想獲得匹配的所有*行的數量,但是我可能只返回一小部分結果集,因爲我正在用LIMIT和OFFSET分頁。編輯的問題更清楚。 – Lansana

+0

@Lansana。 。 。那麼'SQL_CALC_FOUND_ROWS'就是*你正在尋找的東西。您是否閱讀過文檔? 「第二個SELECT返回一個數字,表示在沒有LIMIT子句的情況下,第一個SELECT將返回多少行。」 –

+0

啊,你是對的!我讀錯了。謝謝一堆! – Lansana

0
votes
answers
48 views
+10

的Java的PreparedStatement:com.microsoft.sqlserver.jdbc.SQLServerException:參數指標超出範圍

0

我嘗試使用JAVA 8 Java的PreparedStatement和代碼的打擊的Java的PreparedStatement:com.microsoft.sqlserver.jdbc.SQLServerException:參數指標超出範圍

 Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 
     con = DriverManager.getConnection(URl,user,""); 
     String sql = "Select Grade from XS_KC where Snum=? and Cnum=?"; 
     PreparedStatement pst = con.prepareStatement(sql); 
     pst.setString(1, "020101"); 
     pst.setString(2, "102"); 
     rs = pst.executeQuery(); 
     while(rs.next()) 
     { 
      System.out.print(rs.getString(1)); 
     } 

我得到的fllowing執行SQL查詢錯誤:com.microsoft.sqlserver.jdbc.SQLServerException:parameter index out of range 但是,如果使用

stat = con.createStatement(); 
rs = stat.executeQuery("Select Grade from XS_KC where Snum='020101' and Cnum='101'"); 

我能得到的結果 有什麼不對?

+0

你有不同的列名,不同的值(101 VS 102)......讓我不知道實際的代碼和SQL語句 –

+0

我很抱歉。我忘記更改列名稱。但這不是關鍵。因爲我只是把它翻譯成英文 –

沙发
0
1

查詢中的第一個是Unicode 0x1FFF而不是0x3F00(問號)。嘗試:

String sql = "Select Grade from XS_KC where Snum=? and Cnum=?"; 
+0

謝謝先生。也許這是那些母語不是英語的人的悲傷 –