مغایرت در دادهای یک جدول

[ykm145]

دوستان در جدول زیر رکوردهای که مدل انها برابر ولی تعداد انها از نظر اسمی فرق می کند با چه دستور اس کیو ال می توان بدست اورد



name | model
-------------------
a ' 40
40 ' b مدل 40 تعداد a=b=1
30 ' a
30 ' a
30 ' b مدل 30 تعدادb=1 <>a=2
60 ' a
60 ' a
60 ' b مدل 60 تعداد a=b=2
60 ' b
دستور اس کیو ال :

30 ' a
30 ' a
30 ' b

[mzjahromi]

دوست عزیز شکل جدولی که کشیدید نا خوانا است . جدولتون رو به صورتی خوانا تر بنویسید.

[ykm145]

ممنون دوست عزیز
جدول از دوستون تشکیل شده یکی name ودیگری model
اعداد مربوط به model می باشند و a,b مربوط به name
می خواهیم ردیفهای را بدست بیارم که مدل انها برابر ولی تعداد اسمی انها نابرابر می باشد(دقیقا توضیحات کنار جدول را ببینید متوجه می شوید )
خط چین گذاشتم تا ستونها متمایز شوند

[mzjahromi]

حالا شد.
این جواب


SELECT *
FROM Table1
WHERE (Model IN
(SELECT T1.Model
FROM (SELECT Name, Model, COUNT(*) AS C
FROM Table1
GROUP BY Name, Model) T1,
(SELECT Name, Model, COUNT(*) AS C
FROM Table1
GROUP BY Name, Model) T2
WHERE T1.Model = T2.Model AND
T1.Name <> T2.Name AND
T1.C <> T2.C))

شاید Optimize هم بشه.

[AminSobati]

و کمی ساده تر به این شکل:


select * from t1 where model in (
select Model
from
(select Name, Model, Count(*) as [Count] from
t1 group by Name, Model) tmp
group by Model, [count]
having count(*)=1)

[ykm145]

[ممنون اقای ثباتی و ذوالقدر از زحمات و اطلاعات ارزشمندتان
سوالم را کلی تر مطرح می کنم اگر ممکنه دستورات ان را بنویسید با همان نام واسم جدول m

]چون دراینجا ناخوانا بود در فایل ضمیمه گذاشتم
m فایل توضیح
mogayrat فایل اکسس که شما جدول فوق می باشد

[ykm145]

اقای ثباتی من کدهای شما را تست کردم وجواب گرفتم فقط یه مشکل دارم وان اینکه اول یک کوئری اجرا می کنم وسپس روی ان دستورات شما رااعمال می کنم که در کل باید دو کوئری اجرا کنم حالا اگه ممکنه می خواهم کوئری اول را بادستورات شما تلفیق کنم و برای سرعت در کار و کمتر کردن شلوغی در یک کوئری اجرا کنم

اگه میشه به فایلها یه نگاه بندازید و کمک بزرگی را که به من کردید کامل تر کنید

[AminSobati]

منظورتون اینه که Query من رو به عنوان Subquery استفاده میکنید؟

[ykm145]

درست
ولی کوئری شما پیچیده تر می باشد
من اطلاعات دو جدول را دریک جدول ترکیب کردم اول با یک کوئری ان دسته از اطلاعات جدول ترکیبی که از نظر عدد ونام یکسان نیستند بدست می اید وسپس کوئری ارزشمند شما اجرا می شه
دستورات 1:
SELECT m.gabzid, m.mablag, m.jadval, m.daftarid, m.radifbank, m.datcurrent
FROM m
WHERE (((m.gabzid) Not In (SELECT [gabzid] FROM [m] As Tmp GROUP BY [gabzid],[mablag] HAVING Count(*)>1 And [mablag] = [m].[mablag])))
ORDER BY m.gabzid, m.mablag;

دستورات شما :(t1 نام پرسجوی بالاست )

SELECT *
FROM t1
WHERE (((t1.mablag) In (select mablag
from
(select jadval,mablag, Count(*) as [Count] from
t1 group by jadval,mablag) tmp
group by mablag, [count]
having count(*)=1)));

ترکیب این دو دستور در یک دستور برای یک جدول بنام m حاوی فیلدهای mablag , gabzid, jadval که در jadval نام جدول که اطلاعات ازان گرفته شده مثل bank , daftar
(دو جدول فوق را با insert الحاق کردم نظر شما با union چطوره ؟ )

[ykm145]

اقای ثباتی برای مقایسه بیست هزار رکورد حدودا بیست دقیقه زمان میبره !!!!!!!!!!!!!!
(با اینحال من بازم راضیم چون اگه دو نفر کنارهم چنین کاری را بدون وقفه انجام دهند حدود هفت روز 7 *24 ساعت زمان میبره که معلوم نیست مغایراتها را پیدا کنند مغز و اعصاب و چشمهام که داغون 0000 )
خدا خیرت بده سال خوبی داشته باشید همیشه پابرجا باشید

[mzjahromi]

با Optimize کردن Query ها نتایج شگفت آوری بدست میاد.
من تونستم با Optimization نتیجه 10 دقیقه ای رو به 20 ثانیه تقلیل بدم.

[ykm145]

اقای ذوالقدر دوست عزیز از بذل توجه جنابعالی نیز کمال تشکر را دارم
من بااکسس بعنوان یک ابزار برای کارهام استفاده می کنم وکوئرهارا بصورت گرافیکی می سازم وزیاد هم به دستورات اس کیو ال بعنوان یک برنامه نویس وارد نیستم
ایا Optimization میشه در اکسس نیز پیاده کرد ؟ میشه یه توضیح کلی درباره ان بدی ؟
پیشاپیش سال نو راتبریک واروزی موفقیت و سلامتی برای جنابعالی را دارم

[mzjahromi]

بله اتفاقا بانکهای اطلاعاتی قدرتمند تر کمتر نیاز به Optimization دارن چون خودشون تا حد زیادی این کار رو میکنن. در واقع Optimization چیزی جز استفاده به موقع از شرطها و SubQuery ها نیست. شما باید شروط محدود کننده تر رو اول بنویسید.و حتی المقدور هنگام Join جداول بزرگ اول با محدود کردن اونا حجم اطلاعات رو کم کنید.
متن کامل Query رو بنویسید. (و اگر امکانش براتون هست یه نمونه از بانک حاوی اطلاعات رو بفرستید(البته اگر حجمش بعد از فشرده سازی کمتر از 2 مگابایت بشه))

[ykm145]

ممنون اقای ذوالقدر
فایل را با جدول و دو کوئری مربو ط گذاشتم و تو ضیح اینکه اطلاعات از دو جدول a وb در جدول m ترکیب میشوند برای بدست اوردن مغایرت بین انها

[AminSobati]

درست
ولی کوئری شما پیچیده تر می باشد
من اطلاعات دو جدول را دریک جدول ترکیب کردم اول با یک کوئری ان دسته از اطلاعات جدول ترکیبی که از نظر عدد ونام یکسان نیستند بدست می اید وسپس کوئری ارزشمند شما اجرا می شه
دستورات 1:
SELECT m.gabzid, m.mablag, m.jadval, m.daftarid, m.radifbank, m.datcurrent
FROM m
WHERE (((m.gabzid) Not In (SELECT [gabzid] FROM [m] As Tmp GROUP BY [gabzid],[mablag] HAVING Count(*)>1 And [mablag] = [m].[mablag])))
ORDER BY m.gabzid, m.mablag;

دستورات شما :(t1 نام پرسجوی بالاست )

SELECT *
FROM t1
WHERE (((t1.mablag) In (select mablag
from
(select jadval,mablag, Count(*) as [Count] from
t1 group by jadval,mablag) tmp
group by mablag, [count]
having count(*)=1)));

ترکیب این دو دستور در یک دستور برای یک جدول بنام m حاوی فیلدهای mablag , gabzid, jadval که در jadval نام جدول که اطلاعات ازان گرفته شده مثل bank , daftar
(دو جدول فوق را با insert الحاق کردم نظر شما با union چطوره ؟ )

از Query اول یک View بسازین و این View رو در Query من استفاده کنین

[AminSobati]

اقای ثباتی برای مقایسه بیست هزار رکورد حدودا بیست دقیقه زمان میبره !!!!!!!!!!!!!!
(با اینحال من بازم راضیم چون اگه دو نفر کنارهم چنین کاری را بدون وقفه انجام دهند حدود هفت روز 7 *24 ساعت زمان میبره که معلوم نیست مغایراتها را پیدا کنند مغز و اعصاب و چشمهام که داغون 0000 )
خدا خیرت بده سال خوبی داشته باشید همیشه پابرجا باشید
مطمئنا ایندکسهای مورد نیاز وجود نداره و Query Optimizer ناچاره از الگوریتمهای مشکل استفاده کنه.

[mzjahromi]

خود Query ها قابل Optimize شدن نیستن ولی
من Query ها رو به این شکل تغییر دادم و سرعت t1 از 10 دقیقه به کمتر از 5 ثانیه کاهش یافت
اول اینکه یک جدول موقت برای این Query ایجاد کردم و اطلاعات حاصل از Query رو درون جدول ریختم و روی هر دو فیلدش ایندکس گذاشتم.


INSERT INTO M2
SELECT gabzid AS gabzid, Mablag AS Mablag
FROM m AS Tmp
GROUP BY gabzid, mablag
HAVING count(*)>1;

این باعث میشه که Query فوق(که زمان بر هم هست) 1 بار بیشتر اجرا نشه.
و Query t1 رو به شکل زیر تغییر دادم


SELECT m.gabzid, m.mablag, m.jadval, m.daftarid, m.radifbank, m.datcurrent
FROM m
WHERE (((m.gabzid) Not In (SELECT gabzid FROM M2 Where mablag = m.mablag)))
ORDER BY m.gabzid, m.mablag

نتیجه این روش تاثیر قابل ملاحظه ای تو سرعت داشت

[ykm145]

ممنون اقای ثباتی و اقای ذوالقدر
اقای ذوالقدر منظور از جدولm2 همان .... create veiw m2 as می باشد یا یک جدول معمولی با همان فیلدها ی جدول m 1
ایا در اکسس امکان ساخت view می باشد ؟ چطوری ؟
اگر ممکنه فایلی را که اصلاح کردید برام بفرستید
تشکر از زحماتی که برام کشیدید

[AminSobati]

یکی از شرایط بهینه بودن سرعت Query به این شکله که شما نیازی به I/O اضافی نداشته باشید. آیا همیشه قبل از انجام Query باید به این جدول موقتی مقدار دهی کرد؟ این مقدار دهی در نهایت باعث کند شدن کل پروسه خواهد شد. در بعضی حالتها، یک ایندکس که از پیش ساخته شده، قادره نقش یک جدول موقتی رو ایفا کنه، با این تفاوت که موقع Query گرفتن شما I/O نخواهید داشت چون I/O موقع ورود اطلاعات (قبلا) انجام شده

[ykm145]

اقای ثباتی جای کوئری شما را با کوئری خودم عوض کردم یعنی اول کوئری شما اجرا میشه بعد کوئری من فوق العاده بود سرعت از بیست دقیقه به سی ثانیه کاهش یافت و هم دقیق تر شد وهم یک اشکال جزی را اشکار کرد انهم رکوردهای که از نظر نام جدول و مبلغ و شماره قبض تکراری می باشند بنوعی اثر منفی میذارند یا خودش نمایش داده نمی شوند یا رکوردی دیگری را نمایش می دهند (مثلا در چند رکورد مقدار مبلغ و نام جدول و شماره قبض یکسان باشند )
اگه ممکن شما این دستور را به کوئری من اضافه کنید تا مشکل کاملا حل بشه

رکوردهای بغیر( ازرکوردهای که مقدار مبلغ و شماره قبض انها از هر دو جهت با هم یکی اند ونام جدول ان(jadval )یکی نباشد) نمایش داده شود (درکوئری من شرط یکسان بودن نام جداول قرارداده نشده )

2000 | 123 | a
2000 | 123 | b چون نام جدولها برابرنیستند حذف شود
-----------------------------------------------------
2000 | 123 | a
2000 | 123 | a دراینجا چون از نظر هر سه فیلد یکی اند حذف نشود (نمایش داده شو د)

[ykm145]

اقای ذوالقدر باسلام
من دستورات شما را اجرا کردم رکوردهای مشترک را میدهد در حالیکه من رکوردهای غیر مشترک را میخواهم
در ضمن فکر می کنم در برنامه حسابداری که به بازار دادی با استفاده از این روش امکانات مغایرت گیری بین بانک ودفتر یا شعب ومرکز ... را به برنامه ات اضافه نمائی مطمئن باش پر طرفدارتر میشه

[AminSobati]

"نام جداول" رو متوجه نشدم چی هست. مگه از یک جدول Query نمیگیرین؟ این سه فیلدی که به عنوان Sample قید کردین کدومش شماره قبض و ... هست؟ (من فرصت نکردم ساختار جداول شما رو مطالعه کنم)

[ykm145]

استاد محترم به اینجا اگه دقت کنید متوجه می شوید
دردستورات زیر ان دسته رکوردهای که mablag وgabzid انها یکی اند(برابرندمشترکا)نمایش داده نمیشوند
می خواهیم شرط نابرابری jadval رانیز به ان اضافه کنیم
یعنی mablag و gabzid یکی اند ولی jadval یکی نیستند(منظور از نام جداول همان فیلد jadvalمیباشد که با دو مقدار aوb از دوجدول بانک ودفتر به ان insert into شده )

SELECT m.gabzid, m.mablag, m.jadval, m.daftarid, m.radifbank, m.datcurrent
FROM m
WHERE (((m.gabzid) Not In (SELECT [gabzid] FROM [m] As Tmp GROUP BY [gabzid],[mablag] HAVING Count(*)>1 And [mablag] = [m].[mablag])))
ORDER BY m.gabzid, m.mablag;

این حالت :

jadval|gabzid |mablag
2000 | 123 | a
2000 | 123 | b چون نام جدولها برابرنیستند حذف شود
-----------------------------------------------------
2000 | 123 | a
2000 | 123 | a دراینجا چون از نظر هر سه فیلد یکی اند حذف نشود (نمایش داده شو د)

[AminSobati]

ببینید آقای (یا خانم) ykm145 (ای کاش اسمتون رو میدونستیم!) عزیز،
شما چندین شرط متنوع رو میخواهید یکجا چک کنید در حالیکه به نظر من هنوز ضوابط رو اولویت بندی نکردید (و تمام حالتها رو پیش بینی نکردید). مثلا در این حالت:
a | 123 | 2000
a | 123 | 2000
طبق اولین توضیح شما در این تاپیک، این رکوردها باید نمایش داده شوند چون تعداد a برابر با 2 و تعداد b برابر با 0 است. اما در پست قبلی عنوان کردید که چون از نظر هر سه فیلد یکی هستند، نمایش داده شوند.
در حالت:
2000 | 123 | a
2000 | 123 | b
چون تعداد a و b برابر است، باید حذف شوند ولی دلیل حذف رو برابر نبودن نام جداول ذکر کردید. یا این مثال رو میزنم:
a | 124 | 2000
a | 124 | 3000
الان چه کاری با این دو رکورد باید انجام داد؟ حذف به خاطر یکی نبودن هر سه فیلد یا نمایش به خاطر نامساوی بودن تعداد a و b؟

[ykm145]

اقای ثباتی در تاپیک اول من دوشرط تو mablagوjadval (به ازای هر مبلغ در aمی بایست همان مبلغ به همان تعداد در bموجود باشد)چک میشد نتیجه این رکوردها نمایش داده میشد:(پس از اجرای کوئری شما که دستورات ان را خودتان زحمتش راکشیدیدودر پائین موجود است )

jadval|gabzid |mablag
2000 | 123 | a
2000 | 123 | b مبلغ 20000 تعداد a=1<>b=2
2000 | 123 | b
------------------------
3000 | 124 | a
3000 | 125 | a مبلغ 30000 تعداد a=2<>b=1
3000 | 125 | b
درکوئری که میخواهم ایجاد کنم سه شرط اعمال میشه
انهائیکه mablag وgabzid یکی وتعداد jadval برابر بر رکوردهای بالا اعمال می شوند وجواب ذیل بدست می اید :

jadval|gabzid |mablag
3000 | 125 | a
3000 | 125 | b

مبلغ 3000 و شماره قبض 125 دو رکورد به ازای a=b=1 پس نشان داده میشود
مبلغ 2000وشماره قبض123 سه رکورد به ازای a=1<>b=2پس نشان داده نمی شود
(که با اضافه کردن not بر عکس بدست می ایدیعنی مبلغی که در aهست ودر b نیست مربوط به کدام شماره قبض است وبلعکس )
فکر میکنم مسئله روشنه
اینم دستور شما برای کوئری اول که بیست هزار رکورد را به دوهزار تقلیل میدهد


SELECT *
FROM t1
WHERE (((t1.mablag) In (select mablag
from
(select jadval,mablag, Count(*) as [Count] from
t1 group by jadval,mablag) tmp
group by mablag, [count]
having count(*)=1)));

[mzjahromi]

اقای ذوالقدر باسلام
من دستورات شما را اجرا کردم رکوردهای مشترک را میدهد در حالیکه من رکوردهای غیر مشترک را میخواهم

خوب یه شرط رو معکوس کنید.(هر کدوم که هست)

[ykm145]

ممنون دوست عزیز
با تک تک برعکس کردن جواب های دیگری میدهدچون.زیاد بدستورات ساب کوئری وارد نیستم نمی توانم تغیرات لازم را بدم از طرفی چون مسئله حساس یعنی اگر حتی یک رکورد مغایر هم پیدانشه باید دستی بیست هزار رکورد را چک کنی که حتما هم به جواب نمیرسی !!! به اطلاعات ناقص خودم اطمینان نمی کنم ومنتظر دستورات جنابعالی می مانم
سپاس فراوان از توجه جنابعالی

[mzjahromi]

ببینید اون Query که من جایگزین t1 کردم جوابی دقیقا شبیه به همون t1 خود شما می داد. مگه اون درست نبود؟

[ykm145]

اقای ذوالقدر الگوریتم را عوض کردم چون هم سرعت زیاد شد وهم دقیقتر اگه ممکن به اخرین در خواستم که به اقای ثباتی شرح دادم (سه شرط : مبلغ وشماره قبض یکی وتعداد a, b برابر )نگاه بیندازید شما دستورات را برای دوشرط(مبلغ یکی و(a,b(jadval نابرابر) نوشتید حالا ان را به سه شرط بنویسید مسئله حله
البته من در جدول m یه فلید بنام fish اضافه کردم وان را با یک کوئری به gabz&"#"&mablag اپدیت کردم وکد اقای ثباتی رابرای fish برابر و تعدادa,b نابرابر اجرا کردم(روی کوئری اول اقای ثباتی ) سرعت ده ثانیه شد و اطلاعات دقیق وفیلدهای که خالی بود نیز مورد ارزیابی قرار گرفت
فقط نمی دونم میشه به این روش متکی شد ؟در حالیکه باید یک کوئری اضافه نیز (اپدیت )اجرا بشه !
متشکر

[AminSobati]

در Query که قید کردین، اگر علامت = رو با علامت <> عوض کنید، تمام رکوردهایی بدست میاد که مبلغ یکسان دارند و تعداد جدول a و b مساویه:


SELECT *
FROM t1
WHERE (((t1.mablag) In (select mablag
from
(select jadval,mablag, Count(*) as [Count] from
t1 group by jadval,mablag) tmp
group by mablag, [count]
having count(*)<>1)))

[ykm145]

نوشته شده توسط اقای ثباتی :

در Query که قید کردین، اگر علامت = رو با علامت <> عوض کنید، تمام رکوردهایی بدست میاد که مبلغ یکسان دارند و تعداد جدول a و b مساویه:

اقای ثباتی سه شرط : مبلغ یکسان و شماره قبض یکسان و تعداد جدول aوb برابر
(به علامت <> برای برابری یا عدم نابرابری aوb توجیه هستم )
من به این روش عمل کردم :
در جدول m یه فلید بنام fish اضافه کردم وان را با یک کوئری به gabzid&"#"&mablag اپدیت کردم و دستورات شما را برای مبلغ مساوی و جدول نابرابر اجرا کردم سپس روی این کوئری دوباره دستور شما را برای (fish(gabzid&"#"&mablag برابر و جدول نابرابر اجرا کردم درکل:
سرعت ده ثانیه شد فیلدهای که شماره قبض ان خالی بود ارزیابی ونتایج دقیق بدست امد
فقط نمی دونم میشه به این روش متکی شد ؟در حالیکه باید یک کوئری اضافه نیز (اپدیت )اجرا بشه

[AminSobati]

من این Query رو که مشخص کننده تساوی مبلغ و شماره قبض هستش مینویسم:


SELECT gabzid, mablag,COUNT(*) AS 'TCount' FROM T1
GROUP BY gabzid, mablag
HAVING COUNT(*) % 2 = 0

و حالا Join میکنیم با Query قبلی:



SELECT t1.* FROM t1 JOIN
(SELECT gabzid, mablag,COUNT(*) AS 'TCount' FROM T1
GROUP BY gabzid, mablag
HAVING COUNT(*) % 2 = 0) tmp2
ON t1.gabzid=tmp2.gabzid AND t1.gabzid=tmp2.gabzid
WHERE t1.mablag IN
(SELECT mablag FROM
(SELECT jadval,mablag, Count(*) as [Count] FROM t1
GROUP BY jadval,mablag) tmp
GROUP BY mablag, [count]
HAVING COUNT(*)<>1) order by t1.mablag,t1.jadval,t1.gabzid ;


بعد از اینکه سرعت Query بالا رو چند بار آزمایش کردین، ایندکس زیر رو بسازید و ببینید آیا در سرعتش بهبودی حاصل میشه یا خیر:


CREATE INDEX A_IX1 ON T1(MABLAG,JADVAL,GABZID)

[AminSobati]

در ضمن، Update برای یک Query گرفتن، چرا؟!!

[ykm145]

اقای ثباتی از زحمات جنابعالی بی نهایت سپاسگزارم مشکل حل شد :تشویق: :تشویق: :تشویق: